Java_JDBC及连接池

一、JDBC介绍

jdbc是java操作数据库的方式，本质是一套接口，由sun公司制定的规则，再由各个数据库厂商去实现这个接口，我们成数据库公司写的这套实现类为驱动jar包

二、好处

学习一条API就能够操作所有的关系型数据库
三、基础操作
其中executeUpdata（）用来操作DML，返回值是数据库受影响的行数；executeQuery（）用来操作DQL（select语句）返回值是ResultSet集合，里面包含查询到的数据。

使用prepareStatement：

setXxx的方法：只需要记住setObect(问号的编号, 问号的值)
编号从1开始

resultSet的操作：

1. resultSet.getXxx(String columnName)根据列名获取列值
   Xxx表示列名的属性。
2. boolean next()：游标往下移动一行并返回该行是否有数据
   true有数据
   false没有数据

executeUpdata的使用：

//注册驱动
//如果驱动是8.0版本以上的需要加上“cj”,5.0的不用加
Class.forName("com.mysql.cj.jdbc.Driver";
//建立链接
String url = "jdbc:mysql://localhost:3306/数据库";
String username = "root";
String password = "root";
Connection connection = DriverManger.getConnection(url,username,password);
//获取执行者对象
String sql= "delete from emp where eid=？";
//使用prepareStatement可以用来防止sql注入
Statement pstmt = connection.preparStatement(sql);
pstmt = setObect(问号的编号, 问号的值)
//executeUpdate()返回数据库受影响的行数
int row = pstmt.executeUpdate();

executeQuery的使用：

// 使用到了自己写的工具类，免去了每次建立链接的烦恼
        Connection conn = JdbcUtile.getj().getConn();
        String sql = "select * from users1 where password = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,"123");
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()){
            int uid = resultSet.getInt("uid");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            System.out.println(uid+" "+username+" "+password+" "+nickname);
        }
        resultSet.close();
        pstmt.close();
        conn.close();

JdbcUtile工具类：

/*
* jdbc驱动工具类：
* 省去jdbc的创建连接过程
* */
public class JdbcUtile {
    private static Connection conn;
    private JdbcUtile(){}
    private static JdbcUtile j = new JdbcUtile();
    public static JdbcUtile getj(){
        return j;
    }
    public Connection getConn(){
        return conn;
    }
    // 只需执行一次所以放进静态代码块
static {
//        获取类加载器
        ClassLoader classLoader = JdbcUtile.class.getClassLoader();
//读取properties的配置问价
        InputStream in = classLoader.getResourceAsStream("driver.properties");
        Properties properties = new Properties();
//        读入properties集合中
    try {
        properties.load(in);
    } catch (IOException e) {
        e.printStackTrace();
    }
//        获取value
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
//        开启驱动，获得连接
    try {
        conn = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        e.printStackTrace();
    }
  }
}

properties配置文件：

url=jdbc:mysql://localhost:3306/db4?serverTimezone=GMT%2B8&useSSL=false
username=root
password=root

三、数据库连接池

解决频繁创建链接和销毁链接的烦恼节约时间提高效率

配置连接池工具类DruidUtil：

public class DruidUtil {
   private static DataSource ds;
    static {
        try {
//        类加载器读取配置文件
        Properties properties = new Properties();
        InputStream in = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(in);
//            创建连接池
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
//    获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection = ds.getConnection();
        return connection;
    }
//    释放资源:DML
    public static void close(Connection conn, Statement stmt){
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!= null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
//    释放资源：DQL
    public static void close(Connection conn, Statement stmt, ResultSet rs){
        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();
            }
        }
    }
}

连接池properties配置文件：

url=jdbc:mysql://localhost:3306/homework?serverTimezone=GMT%2B8&useSSL=false
username=root
password=root
driverClassName=com.mysql.cj.jdbc.Driver
#初始化数据连接池
initialSize=5
#数据连接池中最多存在连接数量
maxActive=10
#最大存在时间
maxWait=3000

测试类：

public class DruidTest {
    @Test
    public void Druidtest() throws Exception{
        Connection connection = DruidUtil.getConnection();
        String sql = "select sid,sname,chinese from student where chinese > ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setObject(1,"90");
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()){
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            int chinese = rs.getInt("chinese");
            System.out.println("学号："+sid+"姓名："+sname+"语文成绩："+chinese);
        }
        DruidUtil.close(connection,pstmt,rs);
    }
    @Test
    public void DruidAdd() throws Exception{
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into student(sid,sname,chinese) values(?,?,?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setObject(1,null);
        pstmt.setObject(2,"chen");
        pstmt.setObject(3,99);
        int i = pstmt.executeUpdate();
        if (i>0){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }
        DruidUtil.close(connection,pstmt);
    }
    @Test
    public void DruidUpdate() throws Exception{
        Connection connection = DruidUtil.getConnection();
        String sql = "update student set sgender = ? where sname=?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setObject(1,"男");
        pstmt.setObject(2,"chen");
        int i = pstmt.executeUpdate();
        if (i>0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
    }
    @Test
    public void DruidDelete() throws Exception{
        Connection connection = DruidUtil.getConnection();
        String sql = "delete from student where sname = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setObject(1,"chen");
        int i = pstmt.executeUpdate();
        if (i>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
    }