熟悉JDBC

熟悉JDBC

一、原生JDBC

1、加载数据库驱动

String driver = "com.mysql.cj.jdbc.Driver";//定义驱动名称
Class.forName(driver);

2、创建数据库连接

String url = "jdbc:mysql://127.0.0.1:3306/testjdbc?" +//连接数据库地址
        "characterEncoding=UTF8&serverTimezone=GMT";//编码和时区
String user = "root";//数据库用户名
String pass = "root";//数据库密码
Connection conn = DriverManager.getConnection(url, user, pass);

3、创建Statement对象

Statement stmt = conn.createStatement();

4、执行SQL语句

//SQL语句
String querySql = "SELECT * FROM user";
String insertSql = "INSERT user(username,password) VALUES('test','test')";
String updateSql = "UPDATE user SET username = 'test3' WHERE id = '5'";
String deleteSql = "DELETE FROM user WHERE id = '4'";
System.out.println("====查询====");
ResultSet rs = stmt.executeQuery(querySql);
System.out.println("====增加、修改、删除====");
int insertRow = stmt.executeUpdate(insertSql);
int updateRow = stmt.executeUpdate(updateSql);
int deleteRow = stmt.executeUpdate(deleteSql);

5、处理SQL返回后的结果

while (rs.next()) {
    System.out.println("id:" + rs.getInt("id") +
            "  username:" + rs.getString("username") +
            "  password:" + rs.getString("password"));
}
System.out.println("增加结果：" + insertRow + "条");
System.out.println("修改结果：" + updateRow + "条");
System.out.println("删除结果：" + deleteRow + "条");

6、关闭创建的各个对象

if (rs != null) {
    rs.close();
}
if (stmt != null) {
    stmt.close();
}
if (conn != null) {
    conn.close();
}

总的代码测试testJDBC.java

package jdbc;

import java.sql.*;

/**
 * @author qiuqiu_xqy
 */
public class testJDBC {
    public static void main(String[] args) {
        String driver = "com.mysql.cj.jdbc.Driver";//定义驱动名称
        String url = "jdbc:mysql://127.0.0.1:3306/testjdbc?" +//连接数据库地址
                "characterEncoding=UTF8&serverTimezone=GMT";//编码和时区
        String user = "root";//数据库用户名
        String pass = "root";//数据库密码
        //SQL语句
        String querySql = "SELECT * FROM user";
        String insertSql = "INSERT user(username,password) VALUES('test','test')";
        String updateSql = "UPDATE user SET username = 'test3' WHERE id = '5'";
        String deleteSql = "DELETE FROM user WHERE id = '4'";
        try {
            // 1、加载数据库驱动
            Class.forName(driver);
            // 2、创建数据库连接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 3、创建Statement对象
            Statement stmt = conn.createStatement();
            // 4、执行SQL语句
            System.out.println("====查询====");
            ResultSet rs = stmt.executeQuery(querySql);
            System.out.println("====增加、修改、删除====");
            int insertRow = stmt.executeUpdate(insertSql);
            int updateRow = stmt.executeUpdate(updateSql);
            int deleteRow = stmt.executeUpdate(deleteSql);
            // 5、处理SQL返回后的结果
            while (rs.next()) {
                System.out.println("id:" + rs.getInt("id") +
                        "  username:" + rs.getString("username") +
                        "  password:" + rs.getString("password"));
            }
            System.out.println("增加结果：" + insertRow + "条");
            System.out.println("修改结果：" + updateRow + "条");
            System.out.println("删除结果：" + deleteRow + "条");
            // 6、关闭创建的各个对象
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

二、封装类

封装类DB.java

package jdbc;

import java.sql.*;

/**
 * @author qiuqiu_xqy
 */
public class DB {
    private String url = null;
    private String user = null;
    private String pass = null;
    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;

    //初始化时，加载数据库驱动
    public DB(String driver) {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //url、user、pass的setter、getter方法
    public String getUrl() {
        return url;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public String getUser() {
        return user;
    }
    public void setUser(String user) {
        this.user = user;
    }
    public String getPass() {
        return pass;
    }
    public void setPass(String pass) {
        this.pass = pass;
    }
    //得到Connection对象
    private Connection getConn() {
        try {
            conn = DriverManager.getConnection(url, user, pass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //得到Statement对象
    private Statement getStmt() {
        try {
            if (conn == null) {
                this.getConn();
            }
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }
    //执行SQL查询语句
    public ResultSet executeQuery(String sql) {
        try {
            if (stmt == null) {
                this.getStmt();
            }
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }
    //执行SQL增加、删除、修改语句
    public int executeUpdate(String sql) {
        int row = -1;
        try {
            if (stmt == null) {
                this.getStmt();
            }
            row = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
    //关闭连接
    public void closeDB() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                rs = null;
            }
            if (conn != null) {
                conn.close();
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.print("关闭数据库失败");
        }
    }
}

测试类testDB.java

package jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author qiuqiu_xqy
 */
public class testDB {
    public static void main(String[] args) {
        //创建DB对象并给DB对象的属性赋值
        DB db = new DB("com.mysql.cj.jdbc.Driver");
        db.setUrl("jdbc:mysql://127.0.0.1:3306/testjdbc?characterEncoding=UTF8&serverTimezone=GMT");
        db.setUser("root");
        db.setPass("root");
        //查询操作
        ResultSet rs = db.executeQuery("SELECT * FROM user");
        System.out.println("==查询==");
        try {
            while (rs.next()){
                System.out.println("id:" + rs.getInt("id") +
                        "  username:" + rs.getString("username") +
                        "  password:" + rs.getString("password"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        //修改操作
        int row = db.executeUpdate("UPDATE user SET username = 'test3' WHERE id = '5'");
        System.out.println("修改结果：" + row + "条");
        //不用时，记得关闭连接
        db.closeDB();
    }
}

执行结果

==查询==
id:1  username:张三  password:123456
id:2  username:李四  password:123456
id:3  username:王五  password:123456
id:5  username:test3  password:test
id:6  username:test  password:test
id:7  username:test  password:test
修改结果：1条

注意这儿很容易出现Operation not allowed after ResultSet closed报错
一个stmt多个rs进行操作
那么从stmt得到的rs1,必须马上操作此rs1后,才能去得到另外的rs2,再对rs2操作
不能互相交替使用,会引起rs已经关闭错误