Day 14 JDBC

JDBC

1、简单入门 Statement
2、preparedStatement
3、主键回显
4、批量操作
5、事务
6、Druid

1、简单入门 Statement

步骤:
1、注册驱动
2、创建连接
3、创建 Statement对象
4、编写sql语句并且发送sql语句获得结果集
5、解析结果集
6、释放资源
注意事项都在写下面代码里了

import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.Properties;

public class day1 {
    public static void main(String[] args) throws Exception {
        new test();
        new test();
        //注册驱动  不推荐 会注册两次 new Driver中有静态代码块
        //DriverManager.registerDriver(new Driver());
        //new Driver();//不推荐 不能显示出 是什么驱动 如果是8+ 则是com.mysql.cj.jdbc 如果是5+ 则是com.mysql.jdbc
        Class.forName("com.mysql.cj.jdbc.Driver");  //推荐 通过反射获得类加载
        //建立连接 3种方式 ①
//        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
        // ②
        Properties properties=new Properties();
        properties.put("user","ckytest");
        properties.put("password","123456");
//        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test",properties);
        //③
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=ckytest&password=123456");
        //创建 mql
        Statement statement = ckytest.createStatement();
        //mql 并发送 获取结果集
        String mysql="select * from u_ser";
        //  statement.executeUpdate();  非DML


        ResultSet resultSet = statement.executeQuery(mysql);
        while(resultSet.next()){
            int anInt = resultSet.getInt(1);
//            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println(anInt+"---"+name);
        }
    }
}

2、preparedStatement

步骤:
1、注册驱动
2、创建连接
3、编写sql语句
4、创建preparedStatement 对象并且传入sql语句
5、占位符赋值
从左到右索引从1开始
6、发送sql语句获得结果集
7、解析结果集
8、释放资源

import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class test {
    @Test
   public void testinsert() throws  Exception{
       Class.forName("com.mysql.cj.jdbc.Driver");
       Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
       String mysql="insert into u_ser(id,name) values(?,?);";
       PreparedStatement preparedStatement = ckytest.prepareStatement(mysql);
       preparedStatement.setObject(1,3);
       preparedStatement.setObject(2,"yue");
        int i = preparedStatement.executeUpdate();
        if (i>0)
           System.out.println("成功");
        preparedStatement.close();
        ckytest.close();
   }

    @Test
    public void testupdate() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
        String mysql="update u_ser set name=? where id=?;";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql);
        preparedStatement.setObject(1,"cui");
        preparedStatement.setObject(2,3);
        int i = preparedStatement.executeUpdate();
        if (i>0)
            System.out.println("成功");
        preparedStatement.close();
        ckytest.close();
    }
    @Test
    public void testdelete() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
        String mysql="delete from u_ser where  id=?;";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql);
        //preparedStatement.setObject(1,"cui");
        preparedStatement.setObject(1,3);
        int i = preparedStatement.executeUpdate();
        if (i>0)
            System.out.println("成功");
        preparedStatement.close();
        ckytest.close();
    }

    //查询u_ser表中所有的内容 并保存到 List<map>集合中 key-value
    @Test
    public void testquery() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
        String mysql="select * from u_ser;";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql);
        ResultSet resultSet = preparedStatement.executeQuery();
        //得到表信息
        ResultSetMetaData metaData = resultSet.getMetaData();
        //得到列数量
        int columnCount = metaData.getColumnCount();
        List<Map> list=new ArrayList<>();
        while(resultSet.next()) {
            Map map = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获得结果
                Object object = resultSet.getObject(i);
                //获得列名
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel, object);

            }
            list.add(map);
        }
        System.out.println(list);
        resultSet.close();
        preparedStatement.close();
        ckytest.close();
    }
}

String columnLabel = metaData.getColumnLabel(i);
getColumnLabel 有别名可以获得别名而getColumnName 不能获取别名。
getColumnCount 获取列的数量
getMetaData 获取列的信息

3、主键回显

  /**
     * 
     * 得到自增长的主键
     * 在创建prepareStatement，告知它拿回自增长的主键
     * getGeneratedKeys 得到一个自增长主键的结果 首先通过next 移动光标
     * 之后通过getxx 获取结果，插入一行 肯定只获取一个结果 所以是一行一列 索引为1 即使回显的主键
     */
    @Test
    public void testinsertkey() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "ckytest", "123456");
        String mysql="insert into u_ser(name) values(?);";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql,Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setObject(1,"yue1");
        int i = preparedStatement.executeUpdate();
        if (i>0)
            System.out.println("成功");
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
        generatedKeys.next();
        Object id = generatedKeys.getObject(1);
        System.out.println("回显主键："+id);
        preparedStatement.close();
        ckytest.close();
    }

4、批量操作

在url中添上rewriteBatchedStatements=true，允许批量操作。
sql语句不要加分号
preparedStatement.addBatch();
preparedStatement.executeBatch();

    @Test
    public void testinsert1() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true", "ckytest", "123456");
        String mysql="insert into u_ser(name) values(?)";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql,Statement.RETURN_GENERATED_KEYS);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1,"yue1"+i);
             preparedStatement.executeUpdate();

        }
        long end = System.currentTimeMillis();
        System.out.println("时间为："+(end-start)/1000+"s"); //14s
        preparedStatement.close();
        ckytest.close();
    }
    @Test
    public void testabatchinsert() throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection ckytest = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true", "ckytest", "123456");
        String mysql="insert into u_ser(name) values(?)";
        PreparedStatement preparedStatement = ckytest.prepareStatement(mysql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1,"yue"+i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("时间为："+(end-start)+"s");//0.77s
        preparedStatement.close();
        ckytest.close();
    }

5、事务

原子性一致性隔离性持久性
事务的发生必须是在同一个连接中。
对于事务，我们应该在业务层进行开启和关闭。mysql是自动提交业务，我们可以手动开启或者关闭。
比如转账业务，我们可以在业务层获取一个连接，之后将该连接传入加钱和转钱方法，这样可以确保是一个连接。
事务，我们一般使用try，catch，如果成功就提交，捕捉到错误就回滚。

6、Druid

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

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidTest {
    public void druid1() throws  Exception{
        //创建连接池对象
        DruidDataSource druidDataSource=new DruidDataSource();
        //一些必要和不必要的选项 url,name,pwd,driver都是必须的 连接池数量这些不是必须的
        druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
        druidDataSource.setPassword("123456");
        druidDataSource.setUsername("ckytest");
        druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        druidDataSource.setInitialSize(5);
        druidDataSource.setMaxActive(10);
        //获得一个连接。
        DruidPooledConnection connection = druidDataSource.getConnection();
        //回收
        connection.close();//如果是连接池 则close代表 回收 而不是关闭连接。
    }
    public void druid2() throws  Exception{

        //读取配置文件
        Properties properties=new Properties();
        //只要在src下 就可以使用类加载器
        InputStream resourceAsStream = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(resourceAsStream);//加载到properties中
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //获得一个连接。
        Connection connection = dataSource.getConnection();
        //回收
        connection.close();//如果是连接池 则close代表 回收 而不是关闭连接。
    }
}

Url=jdbc:mysql://127.0.0.1:3306/test
Password=123456
Username=ckytest
DriverClassName=com.mysql.cj.jdbc.Driver

6.1 工具类V1

每次都去初始化连接池太麻烦了，我们封装一个工具类，对外提供一个获取连接和回收连接的方法。

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/*
*   工具类：内部包含一个连接池对象 对外提供连接和回收连接的方法
*  创建连接池对象 应该是一个单例模式 可以使用静态代码块来实现 只加载一次
 */
public class DruidUtils {
    //连接池
    private static DataSource dataSource=null;
    static {
        Properties properties=new Properties();
        InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(resourceAsStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }
    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    public void closeConnection(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6.2 工具类V2

我们的工具类V1,只是单例模式了连接池，但是每次获取连接都是新的连接。
如果我们在Serve层获得一个连接，这个连接还需要传给Dao层来获取，能不能让同一个线程，Serve层和Dao层获取的都是同一个连接，而不用使用参数传递呢？可以使用线程局部变量。只要一个线程里边的属性就是通用的。

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/*
*   工具类：内部包含一个连接池对象 对外提供连接和回收连接的方法
*  创建连接池对象 应该是一个单例模式 可以使用静态代码块来实现 只加载一次
 */
public class DruidUtils {
    //连接池
    private static DataSource dataSource=null;
    private static ThreadLocal<Connection> threadLocal=new ThreadLocal<>();
    static {
        Properties properties=new Properties();
        InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(resourceAsStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }
    public Connection getConnection() throws SQLException {
        Connection connection=threadLocal.get();
        if (connection==null){
            connection = dataSource.getConnection();
            threadLocal.set(connection);
        }
        return connection;
    }
    public void closeConnection() throws Exception{
        Connection connection = threadLocal.get();
        if (connection!=null){
            threadLocal.remove();
            connection.setAutoCommit(true);
            connection.close();
        }
    }
}

6.3

对于Dao层的封装，写一个基础BaseDao，对语句进行封装，可以分为DQL和非DQl两种。

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class A {

    public static int  executUpdate(String sql,Object ... args) throws Exception{
        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i <args.length ; i++) {
            preparedStatement.setObject(i+1,args[i]);
        }
        int i = preparedStatement.executeUpdate();
        if (connection.getAutoCommit()){
            //没有开启事务
            DruidUtils.closeConnection();
        }
        return i;
    }
    public static  <T>  List<T> executQuery(Class<T> tClass,String sql, Object ... args) throws Exception{
        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        if(args!=null&&args.length>0)
        {
            for (int i = 0; i <args.length ; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        //得到表信息
        ResultSetMetaData metaData = resultSet.getMetaData();
        //得到列数量
        int columnCount = metaData.getColumnCount();
        List<T> list=new ArrayList<>();
        while(resultSet.next()) {
            Constructor<T> constructor = tClass.getConstructor();
            T t = constructor.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                //获得结果
                Object object = resultSet.getObject(i);
                //获得列名
                String columnLabel = metaData.getColumnLabel(i);
                Field declaredField = tClass.getDeclaredField(columnLabel);
                declaredField.set(t,object);
            }
            list.add(t);
        }
        if (connection.getAutoCommit()){
            //没有开启事务
            DruidUtils.closeConnection();
        }
        return list;
    }
}

数据库每个表都对应java中的一个实体类，查询时我们返回的是一个装有实体类的集合。

封装过工具类之后的增删改查简化了很多。

import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class test {
    @Test
   public void testinsert() throws  Exception{

       String mysql="insert into u_ser(id,name) values(?,?);";
        int a = A.executUpdate(mysql, 50001, "a");
        System.out.println(a);
   }

    @Test
    public void testupdate() throws  Exception{

        String mysql="update u_ser set name=? where id=?;";
        int a = A.executUpdate(mysql, "cui", 1);
        System.out.println(a);
    }
    @Test
    public void testdelete() throws  Exception{

        String mysql="delete from u_ser where  id=?;";
        int a = A.executUpdate(mysql, 1);
        System.out.println(a);
    }

    //查询u_ser表中所有的内容 并保存到 List<map>集合中 key-value
    @Test
    public void testquery() throws  Exception{
        String mysql="select * from u_ser;";
        List<User> users = A.executQuery(User.class, mysql);
        System.out.println(users);
    }


  
}