JDBC连接Mysql代码优化（IDEA Java EE项目）

怼烎@ 2023-07-12 09:28 95阅读 0赞

# 前期准备 #

[创建一个IDEA Java EE项目][IDEA Java EE]

# 最初版本 #

加载驱动，未关闭Resultset, Statement, Connection

package com.javaee.code;
    
    import java.sql.*;
    
    /**
     *TestJdbc
     */
    public class jdbcTest {
        
    
        public static String getMysql(){
        
            String url = "jdbc:mysql://127.0.0.1:3306/test_db1";//定义url
            // 驱动名字
            String driverName = "com.mysql.cj.jdbc.Driver";
    
            // 查询语句
            String sqlString = "SELECT * FROM employee limit 1";
    
            String result = null;
    
            try {
        
                // 1 加载驱动（3种方法？）
                Class.forName(driverName);
    
                // 2 创建连接
                Connection connection = DriverManager.getConnection(url,"root","123456");
    
                // 3 通过连接获取statement
                Statement  statement = connection.createStatement();
    
                // 4 statement 做一些 增删改查
                ResultSet resultSet = statement.executeQuery(sqlString);
    
                // 5 获取执行结果
                while (resultSet.next()){
        
                    System.out.println((resultSet.getString(4)));
                }
    
            } catch ( SQLException e) {
        //|ClassNotFoundException(方法1）
                e.printStackTrace();
            }
            return null;
    
        public  static void main(String args){
        
            getMysql();
        }
    }

# 改进1：IDEA不用加载驱动 #

DriverManager.setLogWriter(new PrintWriter(System.out));

打印DriverManager到控制台如下图![在这里插入图片描述][20200308124914298.png]

# 改进二：在finally\{\}里关闭变量 #

*  首先在把变量声明移到try\{\}外面  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70]
 *  在finally中依次关闭resultset,statement,connection

finally{
        
                if(null != resultSet){
        
                    try{
        
                        resultSet.close();
                    } catch(SQLException e){
        
                        e.printStackTrace();
                    }
                }
                if(null != statement){
        
                    try{
        
                        statement.close();
                    }catch (SQLException e){
        
                        e.printStackTrace();
                    }
                }
                if(null != connection){
        
                    try{
        
                        connection.close();
                    }catch(SQLException e){
        
                        e.printStackTrace();
                    }
                }
            }

*  运行后抛出异常javax.net.ssl.SSLException

> MESSAGE: closing inbound before receiving peer’s close\_notify

*  解决：在usl后加上&useSSL=false

String url = "jdbc:mysql://127.0.0.1:3306/school";//定义url
    String allUrl = url + "?user=root&password=123456&useSSL=false";

# 改进三：在try()里声明变量，建立连接 #

package com.java.code.jdbc;
    
    import java.io.PrintWriter;
    import java.sql.*;
    
    /**
     *TestJdbc
     * @author Jingqi Wu
     * @date 2020/3/8
     */
    public class jdbcTestV3 {
        
    
        public static String getMysql(){
        
            String url = "jdbc:mysql://127.0.0.1:3306/school";//定义url
    
            String allUrl = url + "?user=root&password=123456";
         
            // 查询语句
            String sqlString = "SELECT * FROM s_student_homework";
    
            try (Connection connection = DriverManager.getConnection(allUrl)){
        
                // 3 通过连接获取statement
                try(Statement statement = connection.createStatement()){
        
                    // 4 statement 做一些 增删改查
                    try(ResultSet resultSet = statement.executeQuery(sqlString)){
        
                        // 5 获取执行结果
                        while (resultSet.next()){
        
                            System.out.println((resultSet.getString(4)));
                        }
                    }
                }
            } catch ( SQLException e) {
        
                e.printStackTrace();
            }
            return null;
        }
    
        public  static void main(String[] args){
        
            getMysql();
        }
    
    }

# 参考资料 #

*  [idea中通过JDBC使用MySQL数据库与常见报错情况][idea_JDBC_MySQL]
 *  https://github.com/wangkaimin/javaee-01

[IDEA Java EE]: https://blog.csdn.net/qq_39753778/article/details/104618555
[20200308124914298.png]: https://img-blog.csdnimg.cn/20200308124914298.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200308135455947.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[idea_JDBC_MySQL]: https://www.cnblogs.com/douzihedouyacai/p/10920504.html