JavaSE(十二)JDBC-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_39593914/article/details/119514012

本文详细介绍了Java JDBC进行数据库操作的六个步骤，包括注册驱动、获取连接、执行SQL、处理结果集等，并讲解了如何防止SQL注入、处理Blob数据、使用批量插入、事务管理以及行级锁。此外，还探讨了Druid数据库连接池的配置与使用，以及Apache-DBUtils库的常见API。最后，总结了JDBC API的一些关键点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

JavaSE(十二)JDBC

提示：以下是本篇文章正文内容，下面案例可供参考

一、操作六步

六步：
1.注册驱动
2.获取数据库连接对象
3.数据库操作对象
4.执行sql
5.处理查询结果集
6.关闭连接

代码：

 public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try {
//            1.注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//            2.获取链接
            String url = "jdbc:mysql://localhost:3306/jjdb?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true";
            String user = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url,user,password);
//            3.获取数据库操作对象
            stmt = conn.createStatement();
//            4.执行sql
            String sql = "insert into users(`name`,age) values('小江',18)";
            int count = stmt.executeUpdate(sql);
            System.out.println(count == 1 ? "保存成功" : "保存失败");
//            5.处理查询结果集
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            6.释放资源        //从小到大释放
            try {
                if(stmt != null){
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

1.注册驱动(类加载)

jabc中Mysql驱动com.mysql.cj.jdbc.Driver源码：

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

直接类加载就可执行静态代码块注册驱动

Class.forName("com.mysql.cj.jdbc.Driver");

2.结果集处理

 			stmt = conn.createStatement();
//            4.执行sql
//            String sql = "insert into users(`name`,age) values('小鹿',19)";
//            int count = stmt.executeUpdate(sql);
//            System.out.println(count == 1 ? "保存成功" : "保存失败");
            String sql = "select * from users";
            rs = stmt.executeQuery(sql);
            while(rs.next()){
//                System.out.println(rs.getString(1) + "|" + rs.getString(2)+ "|" + rs.getString(3));  列序号
                  System.out.println(rs.getString("id") + "|" + rs.getString("name")+ "|" + (rs.getInt("age") + 10));  //结果集列名称

            }

3.防止sql注入(获取预编译数据库操作对象)

//使用一：
        PreparedStatement pstmt = null;
          String sql = "select * from users where age > ?";  //?为占位符
            pstmt = conn.prepareStatement(sql);  //预编译数据库操作对象
//            给占位符赋值
            pstmt.setInt(1,19);
       	rs = pstmt.executeQuery();
//使用二：
		ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1,args[i]);
            }
            rs = ps.executeQuery();
//        获取结果集的元数据         元数据？  String name = "zhang" 修饰数据的数据，String name就是修饰"zhang"的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取结果集列数
            int columnCount = rsmd.getColumnCount();

4.存取Blob

//存入数据库：
 		Connection conn  = JdbcUtils.getConnection();
        String sql ="";
        PreparedStatement ps = conn.prepareStatement(sql);
        FileInputStream is = new FileInputStream(new File("文件路径"));
        ps.setBlob(1,is);	//填充一个流对象
        ps.executeUpdate();
        JdbcUtils.close(conn,ps,null);
//从数据库下载：
		Connection conn  = JdbcUtils.getConnection();
        String sql ="";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        Blob blob =  rs.getBlob("Blob所在列");
        InputStream is = blob.getBinaryStream();
        FileOutputStream fos = new FileOutputStream("本地路径");
        byte[] buffer = new byte[1024];
        int len;
        while((len = is.read(buffer)) != -1){
            fos.write(buffer,0,len);
        }
        JdbcUtils.close(conn,ps,rs);

注意：在指定了相关Blob类型后，还有：xxx too large报错，需要在mysql目录下的my.ini添加max_allowed_packet=16M,修改后重庆mysql服务即可

5.批量插入数据

mysql服务器默认时关闭批处理的，在配置文件的url后面加上		“?rewiteBatchedStatements=true” ,让mysql开启批处理的支持

		Connection conn = JdbcUtils.getConnection();
        //关闭默认提交
        conn.setAutoCommit(false);
        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i <= 20000; i++) {
            ps.setObject(1,"name_" + i);
//            1."攒"sql
            ps.addBatch();
            if(i % 500 == 0){
//                2.执行batch
                ps.executeUpdate();
//                3.清空batch
                ps.clearBatch();
            }
        }
//        最后一次性提交
        conn.commit();
        JdbcUtils.close(conn,ps,null);

6.事务

自动提交事务

//设置手动提交
 conn.setAutoCommit(false);  
 
  //手动提交                
 conn.commit();    
 
	//手动回滚
conn.rollback();

7.行级锁

select * from users for update
最后加for update就是行级锁，也叫悲观锁
乐观锁，支持多线成并发，当修改记录后的版本号，与修改之前的版本号相同时，才会修改成功，不然会回滚

二、数据库连接池

1.druid

properties

url = jdbc:mysql://localhost:3306/jjdb?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true
username = root
password = 123456
driverClassName=com.mysql.jdbc.Driver
initialSize = 5
maxActive = 20
minActive = 5
maxWait = 3000

使用连接池

Properties pros = new Properties();
        InputStream is =  ClassLoader.getSystemClassLoader().getResourceAsStream("./druid.properties");
        pros.load(is);
        DataSource ds = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = ds.getConnection();
        System.out.println(conn);

三、了解其他连接方式

获取连接对象

方式一：

		Driver driver = new com.mysql.jdbc.Driver();
        String url = "jdbc:mysql://localhost:3306/jjdb";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");
        Connection conn = driver.connect(url,info);
        System.out.println(conn);

方式二：

 		String url = "jdbc:mysql://localhost:3306/jjdb";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");
        Connection conn = driver.connect(url,info);
        System.out.println(conn);

方式三：

 		Class clazz =  Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)clazz.newInstance();
        String url = "jdbc:mysql://localhost:3306/jjdb";
        String user = "root";
        String password = "123456";
        DriverManager.registerDriver(driver);
        System.out.println(DriverManager.getConnection(url,user,password));

方式四：

 		Class clazz =  Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jjdb";
        String user = "root";
        String password = "123456";
        System.out.println(DriverManager.getConnection(url,user,password));

方式五(final)：

		InputStream is =  JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

四、Apache-DBUtils

常用API:
	org.apache.commons.dbutils.QueryRunner
	org.apache.commons.dbutils.ResultSertHandler
	org.apache.commons.dbutils.DbUtils

使用：

(1)更新记录

@Test
    public void testUpdate ()  {
        Connection conn = null;
        try{
            QueryRunner queryRunner = new QueryRunner();
            conn = JdbcUtils.getConnection();
            String sql = "insert into users(`name`,age) values(?,?)";
            queryRunner.update(conn,sql,"li",19);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn,null,null);
        }
    }

(2)查询记录

   @Test
   public void testQuery(){
       Connection conn = null;
       try{
           QueryRunner runner = new QueryRunner();
           conn = JdbcUtils.getConnection();
//            String sql = "select * from users where id = ?";
//            BeanHandler<User> handler = new BeanHandler<>(User.class); // 对应一条记录
//            User user = runner.query(conn,sql,handler,1);

//MapListHandler
//            String sql = "select * from users";
//            MapListHandler handler = new MapListHandler();
//            List<Map<String,Object>> list = runner.query(conn,sql,handler);
//            list.forEach(System.out::println);


           String sql = "select * from users";
           BeanListHandler<User> handler = new BeanListHandler(User.class);
           List<User> list = runner.query(conn,sql,handler);
           list.forEach(System.out::println);
       }catch ( Exception e){
           e.printStackTrace();
       }finally {
           JdbcUtils.close(conn,null,null);
       }
   }

(3)特殊值查询

		Connection conn = null;
        try{
            QueryRunner runner = new QueryRunner();
            conn = JdbcUtils.getConnection();
            String sql = "select count(*) from users";
            ScalarHandler handler = new ScalarHandler();     //Scalar    [ˈskeɪlər] 常量
            Long count = (Long)runner.query(conn,sql,handler);   //不同的特殊值 使用不同的强转
            System.out.println(count);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn,null,null);
        }

(4)自定义handler

 public void testQuery(){
        Connection conn = null;
        try{
            QueryRunner runner = new QueryRunner();
            conn = JdbcUtils.getConnection();
            String sql = "select * from users where id = ?";
            ResultSetHandler<User> handler = new ResultSetHandler<User>() {
                @Override
                public User handle(ResultSet rs) {
                    User user = null;
                    try{
                        ResultSetMetaData rsmd = rs.getMetaData();
                        //获取结果集列数
                        int columnCount = rsmd.getColumnCount();
                        if(rs.next()){
                            user = User.class.getDeclaredConstructor().newInstance();
                            for (int i = 0; i < columnCount; i++) {
                                Object columnValue = rs.getObject(i + 1);
                                String columnName = rsmd.getColumnLabel(i + 1);
                                //通过反射赋值
                                Field field = User.class.getDeclaredField(columnName);
                                field.setAccessible(true);
                                field.set(user,columnValue);
                            }
                        }
                    }catch ( Exception e){
                        e.printStackTrace();
                    }
                    return user;
                }
            };
            User user = runner.query(conn,sql,handler,1);
            System.out.println(user);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn,null,null);
        }

    }

(5)关闭资源

//需要try
		try {
                DbUtils.close(conn);
                //DbUtils.close(rs);
                //DbUtils.close(ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
//悄悄的关，不需要try，自己内部try了
 DbUtils.closeQuietly(conn);
  DbUtils.closeQuietly(ps);
   DbUtils.closeQuietly(rs);

(6)自定义封装

public class JdbcUtils {
    private static DataSource dataSource;
    static {
        try{
            Properties properties = new Properties();
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }catch (Exception e){
        e.printStackTrace();
    }
    }

    private JdbcUtils(){}

    /**
     * 数据库连接池
     * @return  当返回null时，获取连接失败
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     *
     * @param clazz 结果包装类的class
     * @param sql   预编译查询sql
     * @param args  占位符参数
     * @param <T>   结果包装类
     * @return
     */
    public static <T> List<T> Query(Connection conn,Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1,args[i]);
            }
            rs = ps.executeQuery();
//        获取结果集的元数据         元数据？  String name = "zhang" 修饰数据的数据，String name就是修饰"zhang"的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取结果集列数
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            while(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    // 获取每个列列名
//                    String columnName = rsmd.getColumnName(i + 1);
                    //获取结果集的列名：
                    String columnName = rsmd.getColumnLabel(i + 1);
                    //通过反射赋值
                    Field field = User.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,rs);
        }
        return null;
    }

    /**
     * @conn            连接对象
     * @param sql     需要执行的预编译sql语句
     * @param args      填充的占位符参数
     */
    public static void Update(Connection conn,String sql,Object...args){
        PreparedStatement ps = null;
        try{
            //预编译sql语句
            ps =  conn.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length ; i++) {
                ps.setObject(i + 1,args[i]);
            }
            //执行
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //关闭资源
            close(null,ps,null);
        }
    }

    /**
     * 用于查询特殊值方法
     * @param conn
     * @param sql
     * @param args
     * @param <E>
     * @return
     */
    public <E> E getValue(Connection conn,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1,args[i]);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                return (E)rs.getObject(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(null,ps,rs);
        }
        return null;
    }

    public static void close(Connection conn, Statement ps, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}