JDBCUtils工具类

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

JDBCUtils工具类

---

package datasource.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

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

public class JDBCUtils {
        //1.定义成员变量
        private static DataSource ds;

        static {
                try {
//                        System.out.println(JDBCUtils.class.getClassLoader().getResourceAsStream("durid.properties"));
                        //2.加载配置文件
                        Properties pro = new Properties();
                        pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("durid.properties"));
                        //3.获取DataSouce
                        ds = DruidDataSourceFactory.createDataSource(pro);
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (Exception e) {
                        e.printStackTrace();
                }
        }

        public static Connection getConnection() throws SQLException {
                //4.连接
                return ds.getConnection();
        }

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

                if (stmt != null){
                        try {
                                stmt.close();
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                }

                if (conn != null){
                        try {
                                conn.close();
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                }
        }

        public static void close(Statement stmt,Connection conn){
//                if (stmt != null){
//                        try {
//                                stmt.close();
//                        } catch (SQLException e) {
//                                e.printStackTrace();
//                        }
//                }
//
//                if (conn != null){
//                        try {
//                                conn.close();
//                        } catch (SQLException e) {
//                                e.printStackTrace();
//                        }
//                }
                close(null,stmt,conn);//简化代码
        }

        //获取连接池方法
        public static DataSource getDataSource(){
                return ds;
        }
}
增删查改方法:

package jdbctemplate;

import datasource.utils.JDBCUtils;
import domian.Emp;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.beans.Transient;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class JdbcTemplateDemo2 {

    // Junit单元测试可以让方法独立执行
    // 修改一条记录
    @Test
    public void test(){

        // 获取jdbc连接对象
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        // 定义sql语句
        String sql = "update emp set name = ?,age = ? where id = ?";
        // 执行sql语句
        int count = template.update(sql,"成龙",100,1);
        System.out.println(count);
    }
    // 增加一条记录
    @Test
    public void test2(){
        //获取jdbc连接对象
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        //定义sql语句
//        String sql = "insert into emp values(4,'荣誉','404')";
        String sql = "insert into emp values(?,?,?)";//防止sql注入
        // 执行sql语句
        int count = template.update(sql,5,"合法","1024");
        System.out.println(count);
    }
    JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    // 删除刚刚添加的记录
    @Test
    public void test3(){
        //获取jdbc连接对象
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "delete from emp where id = ?";//防止sql注入
        // 执行sql语句
        int count = template.update(sql,5);
        System.out.println(count);
    }

    // 查询一条记录将其封装为map集合
    @Test
    public void test4(){
        String sql = "select * from emp where id = ?";//防止sql注入

        Map<String, Object> maps = template.queryForMap(sql, 4);
        // 执行sql语句
        System.out.println(maps);
    }

    // 查询所有记录,将其封装为list
    @Test
    public void test5(){
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> stringObjectMap: list) {
            System.out.println(stringObjectMap);
        }
    }

    // 查询所有记录,并将其封装为Emp对象的list集合
    @Test
    public void test6(){
        String sql = "select * from emp";
//        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
//            @Override
//            public Emp mapRow(ResultSet rs, int i) throws SQLException {
//                Emp emp= new Emp();
//                int id = rs.getInt("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//
//                emp.setId(id);
//                emp.setNAME(username);
                emp.setNAME(password);
//                return emp;
//            }
//        });
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp: list) {
            System.out.println(emp);
        }
    }

    //查询总记录数
    @Test
    public void test7(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql,Long.class);
        System.out.println(total);
        }
}