JdbcTemplate操作数据库

一、JdbcTemplate（概念和准备）

1、什么是JdbcTemplate

Spring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库操作

2、准备工作

（1）引入相关jar包或在Maven导入依赖

<dependency>
    <groupId>mysqlgroupId>
    <artifactId>mysql-connector-javaartifactId>
    <version>5.1.46version>
dependency>
<dependency>
    <groupId>junitgroupId>
    <artifactId>junitartifactId>
    <version>4.13.2version>
    <scope>testscope>
dependency>
<dependency>
    <groupId>org.springframeworkgroupId>
    <artifactId>spring-jdbcartifactId>
    <version>5.3.22version>
dependency>
<dependency>
    <groupId>org.springframeworkgroupId>
    <artifactId>spring-coreartifactId>
    <version>5.3.22version>
dependency>
<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>druidartifactId>
    <version>1.2.8version>
dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

（2）在spring配置文件配置数据库连接池

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/user_db?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    bean>
1
2
3
4
5
6
7

（3）配置JdbcTemplate对象，注入DataSource

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

    
    <property name="dataSource" ref="dataSource">property>
bean>
1
2
3
4
5
6

（4）创建service类，创建dao类，在dao注入jdbcTemplate对象

@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;
}

1
2
3
4
5
6
7

//BookDao接口
public interface BookDao {
}

1
2
3
4

@Repository
public class BookDaoImpl implements BookDao{
    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}
1
2
3
4
5
6

二、JdbcTemplate操作数据库(增删改)

1、对应数据库创建实体类

public class User {
    private int userId;
    private String userName;
    private String ustatus;

    //Getter和Setter方法
    ...
}
1
2
3
4
5
6
7
8

2、编写service和dao

（1）在dao进行数据库添加操作

//bookDao接口
public interface BookDao {
     //添加信息
    public void add(Book book);

    //修改信息
    public void update(Book book);

    //删除信息
    public void delete(int id);
    
}


//业务逻辑层
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;

    //添加的方法
    public void addBook(Book book){
        bookDao.add(book);
    }
    //修改的方法
    public void updateBook(Book book){
        bookDao.update(book);
    }
    //删除的方法
    public void deleteBook(int id){
        bookDao.delete(id);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

（2）调用JdbcTemplate对象里面update方法实现添加操作

有两个参数

• 第一个参数：sql语句
• 第二个参数：可变参数，设置sql语气值

//dao接口实现类
@Repository
public class BookDaoImpl implements BookDao{
    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

     @Override
    public void add(Book book) {
        //1、创建sql语句insert into t_book values(?,?,?)
        String sql = "insert into user_db.t_book values(?,?,?)";
        //2、调用方法实现
        Object[] args = {book.getUserId(), book.getUserName(), book.getUstatus()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println(update);

    }

    @Override
    public void update(Book book) {
        String sql = "update user_db.t_book set user_name=?,ustatus=? where user_id=?";
        Object[] args = {book.getUserName(), book.getUstatus(),book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void delete(int id) {

        String sql = "delete from user_db.t_book where user_id=?";

        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

3、测试类

//增加信息测试
@Test
    public void MyTest(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUserId(1);
        book.setUserName("java");
        book.setUstatus("a");
        bookService.addBook(book);
    }
//修改信息测试
  @Test
  public void MyTest02(){
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = new Book();
    book.setUserId(1);
    book.setUserName("酷小亚");
    book.setUstatus("abc");
    bookService.updateBook(book);
  }
//删除信息测试
  @Test
  public void MyTest03(){
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);

    bookService.deleteBook(1);
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

三、JdbcTemplate操作数据库（查询）

1、对应数据库创建实体类

public class User {
    private int userId;
    private String userName;
    private String ustatus;

    //Getter和Setter方法
    ...
    //toString 方法
}
1
2
3
4
5
6
7
8
9

2、编写service和dao

（1）在dao进行数据库添加操作

//bookDao接口
public interface BookDao {
     //查询信息
    void select(int id);  
}

//业务逻辑层
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;

   //查询的方法
    public void selectBook(int id){
        bookDao.select(id);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（2）调用JdbcTemplate对象里面queryForObject方法实现添加操作

//dao接口实现类
@Repository
public class BookDaoImpl implements BookDao{
    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

     @Override
    public void select(int id) {
        String sql = "select * from user_db.t_book where user_id = ?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        System.out.println(book);       
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3、测试类

//增加信息测试
@Test
  public void MyTest04(){
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
   bookService.selectBook(2);
  }
1
2
3
4
5
6
7