Spring JDBC

Spring框架对Spring的简单封装。提供一个JDBCTemplate对象简化JDBC开发

*步骤：

        1、导入jar包

        2、创建JDBCTemplate对象。依赖于数据源DataSource

                *JdbcTemplate template=new JdbcTemplate(ds);

        3、调用JdbcTemplate的方法来完成CRUD的操作

                *update()：执行DML语句。增删改语句

                *queryForMap():查询结果，将结果集封装为map集合

                *queryForList():查询结果，将结果集封装为list集合

                *query():查询结果，将结果封装为JavaBean对象

                *queryForobject：查询结果，将结果封装为对象

import com.itheima.utils.JDBCutils;
import org.springframework.jdbc.core.JdbcTemplate;

public class JDBCTemplatedemo1 {
    public static void main(String[] args) {
        //1、导入jar包
        //2、创建JDBCTemplate对象
        JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCutils.dataSource());
        //3、调用方法
        String sql="update account set money=60000 where name=?";
        int count=jdbcTemplate.update(sql,"李四");
        System.out.println(count);
    }
}

import com.itheima.domain.Account;
import com.itheima.utils.JDBCutils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;

import javax.activation.DataSource;
import java.util.List;
import java.util.Map;

public class JDBCTemplatedemo2 {
    //获取jdbctemplet对象，
    private JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCutils.dataSource());
    //junit单元测试，可以让方法独立执行
        //修改张三的money为10000
    @Test
       public void test1(){
       //获取jdbctemplet对象，
        //2、定义sql
        String sql="update account set money=80000 where name='张三'";
        //3、执行sql
        int count=jdbcTemplate.update(sql);
        System.out.println(count);

       }
       @Test
        public void test2(){
        String sql="insert into account (name,money) values (?,?)";
        int count=jdbcTemplate.update(sql,"钱钱",90000);
        System.out.println(count);

}
@Test
    public void test3(){
String sql="delete from account where name=?";
int count=jdbcTemplate.update(sql,"钱钱");
        System.out.println(count);
    }
    @Test
    public void test4(){
        String sql="select * from account where name=?";
       Map<String,Object> map= jdbcTemplate.queryForMap(sql,"张三");
        System.out.println(map);
        //{name=张三, money=80000}
    }
    @Test
    public void test5(){
        String sql="select * from account ";
        List<Map<String,Object>> list= jdbcTemplate.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);
            /**
             * {name=张三, money=80000}
             * {name=李四, money=60000}
             * {name=王五, money=30000}
             * {name=王五, money=30000}
             */
        }
        }
        //查询所有的记录，将其封装为account对象的list集合
    @Test
        public void test6(){
        String sql="select * from account ";
        List<Account>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Account>(Account.class));
            for (Account account : list) {
                System.out.println(account);
            }
    }
    /**
     * Account{name='张三', money=80000}
     * Account{name='李四', money=60000}
     * Account{name='王五', money=30000}
     * Account{name='王五', money=30000}
     */
    
    }

public class Account {
    private String name;
    private int money;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }

    public Account(String name, int money) {
        this.name = name;
        this.money = money;
    }

    public Account() {
    }

    @Override
    public String toString() {
        return "Account{" +
                "name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Collections;
import java.util.Properties;

/**
 * jdbc的连接池的工具类
 */
public class JDBCutils {
    //1、定义成员变量
  private static   DataSource ds;
  static{
      try {
          //加载配置文件
          Properties properties=new Properties();
          properties.load(JDBCutils.class.getClassLoader().getResourceAsStream("Durid.properties"));
          //获取datasource
          try {
              ds= DruidDataSourceFactory.createDataSource(properties);
          } catch (Exception e) {
              e.printStackTrace();
          }
      } catch (IOException e) {
          e.printStackTrace();
      }
  }
  //获取连接的方法
    public static Connection conn() throws SQLException {
        return ds.getConnection();
    }
   public static void close(Statement statement,Connection connection){
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
       if (connection!=null){
           try {
               statement.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }
   }
    public static void close(ResultSet resultSet,Statement statement, Connection connection){
        if (resultSet!=null){
            try {
                resultSet.close();//归还连接
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static DataSource dataSource(){
      return ds;
    }
}