jdbcTemplate操作数据库

假设数据库中有一个表格叫做book,有三个属性分别为userId,username,usatus。可以先定义一个Book类

1实现数据库的添加删除修改 

@Component
public class Book{
    @Value(value="1")
    private String userId;
    @Value(value="java")
    private String userName;
    @Value(value="a")
    private String ustatus;
    public void setUserId(String id){
        userId=id;
}
    public void setUserName(String name){
        userName=name;
}
    public void setUstaus(String us){
        ustatus=us;
}
    public String getUserId(){
        return userId;
}
    public String getUserName(){
        return userName;
}
    public String getUstatus(){
        return ustatus;
}
}

完成BookService类

@Component
public class BookService{
    @Autowired
    private BookDao bookDao;
    public void add(Book book){
        bookDao.add(book);
}
    public void update(Book book){
        bookDao.update(book);
}
    public void delete(String id){
        bookDao.delete(id);
}
}

完成BookDaoImpl类

public interface BookDao{
    void add(Book book);
}
@Component
public class BookDaoImpl implements BookDao{
    @AutoWired
    private JdbcTemplate jdbcTemplate;
    public void add(Book book){
        String sql="insert into book values(?,?,?)";
        int re=jdbcTemplate.update(sql,book.getUserId(),book.getUserName(),book.getUstatus());
        System.out.println(re);
}
    public void update(Book book){
        String sql="update book set userstatus=?,username=? where userid=?";
        
        int re=jdbcTemplate.update(sql,book.getUstatus,book.getUserName(),book.getUserId());
        System.out.println(re);
}
    public void delete(String id){
        String sql="delete from book where userid=?";
        int re=jdbcTemplate.update(sql,book.getUserId());
        System.out.println(re);
}
}

public class Test0{
    @Test
    public void test(){
        ApplicationContext con=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bs=con.getBean("bookService",BookService.class);
        Book book=con.getBean("book",Book.class);
        bs.add(book);
        bs.update(book);
        bs.delete(book.getUserId());
}
}

2实现查询功能

查询结果返回一个值

@Component
public class BookService{
    @Autowired
    private BookDao bookDao;
    public int find(){
        return bookDao.selectCount();
}
}

public interface BookDao{
    int selectCount();
}
@Component
public class BookDaoImpl implements BookDao{
    @AutoWired
    private JdbcTemplate jdbcTemplate;
        public int selectCount(){
            String sql="select count(*) from book";
            int count=jdbcTemplate.querryForObject(sql,Integer.class);
            return count;
}
}

测试代码

public class Test(){
    public void test(){
         ApplicationContext con=new ClassPathXmlApplicationContext("bean1.xml");
        BookService bs=con.getBean("bookService",BookService.class);
        bs.find();
}
}

 查询结果返回一个对象

@Component
public class BookService{
    @Autowired
    private BookDao bookDao;
    public Book findOne(){
        return bookDao.findBookInfo(String id);
}
}

public interface BookDao{
    Book findBookInfo(String id);
}
@Component
public class BookDaoImpl implements BookDao{
    @AutoWired
    private JdbcTemplate jdbcTemplate;
        public Book findBookInfo(String id){
            String sql="select * from book where useid=?";
            return jdbcTemplate.querryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);//会新建Book对象并将查询结果通过set方法给对象属性赋值
}
}

查询结果返回集合

@Component
public class BookService{
    @Autowired
    private BookDao bookDao;
    public List<Book> findAll(){
        retrun bookDao.findAllBook();
}
}

public interface BookDao{
    List<Book> findAllBook();
}
@Component
public class BookDaoImpl implements BookDao{
    @AutoWired
    private JdbcTemplate jdbcTemplate;
    public List<Book> findAllBook(){
        String sql="select * from book";
        List<Book> re=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
//先将每条查询记录放到Book对象中，再将Book对象放入集合中
        return re;
}

3实现批量操作

@Component
public class BookService{
    @Autowired
    private BookDao bookDao;
    public void batchAdd(List<Objct[]> batch){
        bookDao.batchAddBook(batch);
}
    public void batchUpdate(List<Objct[]> batch){
        bookDao.batchUpdateBook(batch);
}
    public void batchDelete(List<Objct[]> batch){
        bookDao.batchDeleteBook(batch);
}
}

public interface BookDao{
    void batchAddBook(List<Object[]> batch);
    void batchUpdateBook(List<Object[]> batch);
    void batchDeleteBook(List<Object[]> batch);
}
@Component
public class BookDaoImpl implements BookDao{
    @AutoWired
    private JdbcTemplate jdbcTemplate;
    public void batchAddBook(List<Object[]> batch){
//batch中的数根据sql语句确定
        String sql="insert into book values(?,?,?)";
        int[] re=jdbcTemplate.batchUpdate(sql,batch);
        System.out.println(Arrays.toString(re));
}
    public void batchUpdateBook(List<Object[]> batch){
        String sql="update book set username=?,ustatus=? where userid=?";
        int[] re=jdbcTemplate.batchUpdate(sql,batch);
        System.out.println(Arrays.toString(re));
}
    public void batchDeleteBook(List<Object[]> batch){
        String sql="delete from book where userid=?";
        int[] re=jdbcTemplate.batchUpdate(sql,batch);
        System.out.println(Arrays.toString(re));
}
}

xml配置文件https://blog.csdn.net/m0_61465063/article/details/122341099?spm=1001.2014.3001.5501