Druid 和 Spring JDBC

01. 基础准备

　　mysql连接库： mysql-connector-java-8.0.23.jar

　　测试单元库：   junit-4.5.jar

02. Druid的准备

　　>> 导入Jar包：  druid-1.1.22.jar

　　>> 配置 druid.properties : 

　　　　

 

03. JDBCTemplate 准备

　　>> 导入 Jar 包:

　　　　　　spring-beans-5.3.6.jar

　　　　　　spring-core-5.3.6.jar

　　　　　　spring-jdbc-5.3.6.jar

　　　　　　spring-tx-5.3.6.jar

　　　　　　commons-logging-1.2.jar

 

04. Druid连接池准备　　

　　>> 公用单元的配置　　

public class JDBCUtils {
    private static DataSource ds;
    static {
        try {
            Properties pro = new Properties();
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //返回连接池对象
    public static DataSource getDataSource(){
        return ds;
    }

    //返回连接对象
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //资源释放
    public static void Close(Statement stmt,Connection con){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void Close(ResultSet rs, Statement stmt, Connection con){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        Close(stmt,con);
    }
}

 

 05. DML 演示

@Test
    public void Test01() throws SQLException {
        //取Template
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        //配置 SQL
        String sql="Insert into user(username,birthday,sex,address) Values (?,?,?,?)";

        //执行
        Date date = new Date();
        int i = template.update(sql, "张三丰", date, "男", "湖北");
        assert i==1;
    }

    @Test
    public void Test02(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "update user set address=? where id=?";
        int i = template.update(sql, "湖南", 46);
        System.out.println(i);
    }

    @Test
    public void Test03(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "Delete from user where id=?";
        int i = template.update(sql, 46);
        System.out.println(i);
    }

 

06. DQL 演示　　

@Test
    public void Test04(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "select * from user where id=?";
        Map<String, Object> map = template.queryForMap(sql, 45);
        System.out.println(map);
    }

    @Test
    public void Test05(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "select * from user where id<?";
        List<Map<String, Object>> maps = template.queryForList(sql, 0);
        System.out.println(maps);
    }

    @Test
    public void Test06(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "select * from user ";
        List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }