目录
使用spring的jdbc-Template学习
/    

使用spring的jdbc-Template学习

spring的jdbc_template的入门例子

数据源

具体配置见applicationContext.xml

c3p0数据源

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

Druid数据源

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
  • 导入jar

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.2.0.RELEASE</version>
            </dependency>
    
  • 导入mysql驱动

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.43</version>
            </dependency>
    
  • 数据库

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) DEFAULT NULL,
      `password` varchar(255) DEFAULT NULL,
      `address` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
    
    
    
  • 配置数据源

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    
    <!--    配置数据源-->
        <bean id="springDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!--       数据库驱动 -->
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql:///jdbc_template"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </bean>
    
    
        <!--    配置Druid数据源-->
        <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <!--       数据库驱动 -->
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql:///jdbc_template"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </bean>
    
        <!--    配置c3p0数据源-->
        <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <!--       数据库驱动 -->
            <property name="driverClass" value="com.mysql.jdbc.Driver"/>
            <property name="jdbcUrl" value="jdbc:mysql:///jdbc_template"/>
            <property name="user" value="root"/>
            <property name="password" value="123456"/>
        </bean>
    
    
    <!--    配置jdbc模板-->
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="c3p0DataSource"/>
        </bean>
    
        <bean id="user" class="cn.lacknb.beans.User">
            <constructor-arg index="0" value="test01"/>
            <constructor-arg index="1" value="123456"/>
            <constructor-arg index="2" value="安阳师范学院老校区"/>
        </bean>
    
        <bean id="userDao" class="cn.lacknb.dao.UserDaoImpl">
            <property name="jdbcTemplate" ref="jdbcTemplate"/>
        </bean>
        <bean id="service" class="cn.lacknb.service.UserServiceImpl">
            <property name="userDao" ref="userDao"/>
        </bean>
    
    </beans>
    
  • User.java

    package cn.lacknb.beans;
    
    public class User {
        private Integer id;
        private String username;
        private String password;
        private String address;
    
        public User(String username, String password, String address) {
            this.username = username;
            this.password = password;
            this.address = address;
        }
    
        public User() {
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }
    
    
  • UserDao.java

    package cn.lacknb.dao;
    
    import cn.lacknb.beans.User;
    
    import java.util.List;
    
    public interface UserDao {
    
        int addUser(User user);
        int deleteUser(Integer id);
        int updateUser(User user);
        User find(Integer id);
        List<User> findAll();
    
    }
    
    
  • UserImpl.java

    package cn.lacknb.dao;
    
    import cn.lacknb.beans.User;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    
    import java.util.List;
    
    public class UserDaoImpl implements UserDao {
    
        private JdbcTemplate jdbcTemplate;
    
        public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }
    
        public int addUser(User user) {
            String sql = "insert into user(username, password, address) values(?, ?, ?)";
            return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getAddress());
        }
    
        public int deleteUser(Integer id) {
            String sql = "delete from user where id=?";
            return jdbcTemplate.update(sql, id);
        }
    
        public int updateUser(User user) {
            String sql = "update user set username=?, password=?, address=? where id = ?";
            return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getAddress(), user.getId());
        }
    
        public User find(Integer id) {
            String sql = "select * from user where id = ?";
            RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
            return (User) jdbcTemplate.queryForObject(sql, rowMapper, id);
        }
    
        public List<User> findAll() {
            String sql = "select * from user";
            RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
            return jdbcTemplate.query(sql, rowMapper);
        }
    }
    
    
  • UserService.java

    package cn.lacknb.service;
    
    import cn.lacknb.beans.User;
    
    import java.util.List;
    
    public interface UserService {
    
        int addUser(User user);
        int deleteUser(Integer id);
        int updateUser(User user);
        User find(Integer id);
        List<User> findAll();
    
    }
    
    
  • UserServiceImpl.java

    package cn.lacknb.service;
    
    import cn.lacknb.beans.User;
    import cn.lacknb.dao.UserDaoImpl;
    
    import java.util.List;
    
    public class UserServiceImpl implements UserService {
    
    
        private UserDaoImpl userDao;
    
        public void setUserDao(UserDaoImpl userDao) {
            this.userDao = userDao;
        }
    
        public int addUser(User user) {
            return userDao.addUser(user);
        }
    
        public int deleteUser(Integer id) {
            return userDao.deleteUser(id);
        }
    
        public int updateUser(User user) {
            return userDao.updateUser(user);
        }
    
        public User find(Integer id) {
            return userDao.find(id);
        }
    
        public List<User> findAll() {
            return userDao.findAll();
        }
    }
    
    
  • MyTest.java

    package cn.lacknb.test;
    
    import cn.lacknb.beans.User;
    import cn.lacknb.service.UserServiceImpl;
    import org.junit.Before;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    import java.util.List;
    
    public class MyTest {
    
        ApplicationContext context;
        UserServiceImpl service;
    
        @Before
        public void before() {
            context = new ClassPathXmlApplicationContext("applicationContext.xml");
            service = (UserServiceImpl) context.getBean("service");
        }
    
        @Test
        // 添加用户
        public void test01() {
    
            User user = (User) context.getBean("user");
            System.out.println("改变的行数为: " + service.addUser(user));
        }
    
        // 删除用户
        @Test
        public void test02() {
            // 删除主键为1的用户
            System.out.println("影响的行数: " + service.deleteUser(1));
        }
    
        // 修改用户
        @Test
        public void test03(){
            User user = (User) context.getBean("user");
            // 修改2号 的 username 为张三
            user = service.find(2);
            user.setUsername("张三");
            System.out.println("影响的行数为: " + service.updateUser(user));
        }
    
        // 查询一个用户
        @Test
        public void test04(){
            // 查找2号用户
            User user = service.find(2);
            System.out.println(user);
        }
    
        // 查找所有用户
        @Test
        public void test05(){
            List<User> list = service.findAll();
            for (User user : list){
                System.out.println(user);
            }
        }
    
    }
    
    

标题:使用spring的jdbc-Template学习
作者:gitsilence
地址:https://blog.lacknb.cn/articles/2019/11/01/1577974165060.html