Java操作MySQL篇——CURD

JDBC驱动

mysql5.0版本驱动：名称为：com.mysql.jdbc.Driver 。连接： mysql-connector-java-5.1.39-bin.jar

mysql8.0版本驱动：名称为：com.mysql.cj.jdbc.Driver。连接： mysql- connector-java-8.0.16.jar

两者的区别：
1 名称不同；
2 mysql8.0以上按本不需要建立SSL连接，但是需要显示关闭，需要指明useSSL = false；
3 mysql8.0 需要设置CST
4 注意：mysql5.0版本的驱动被废弃，推荐使用com.mysql.cj.jdbc.Driver

IDEA连接jdbc方式

1 在projecct 或 model中新建library文件夹（package），将mysql驱动包（jar）放在library中；

2 File-Settings-Project Structure 或者 Ctrl+A+Shift+S

3 添加包引用 ，点击【+】，选择JARs or directories

数据库操作

数据库连接信息：

/**
 * MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL（URL 需要显示不建立 SSL，还要设置 CST）
 */
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
// url 格式：jdbc:mysql://ipAddress:Port/databaseName?useSSL=false&serverTimezone=UTC
static final String DB_URL = "jdbc:mysql://192.168.42.19:3306/tesjdbc?useSSL=false&serverTimezone=UTC";

static final String USER = "root";
static final String PWD = "sa";

增（Insert）

public static boolean insertUserInfo(ArrayList<TestModel> list) {
        Connection connection = null;
        Statement statement = null;
        PreparedStatement pres = null;
        try {
            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DB_URL, USER, PWD);
            if (connection == null) {
                return false;
            }
            statement = connection.createStatement();
            if (statement == null) {
                return false;
            }
            // 设置自动提交 false
            connection.setAutoCommit(false);

            ArrayList<String> sqls = new ArrayList<String>();
            for (TestModel model : list) {
                // 注意 StringBuffer 与 StringBuilder 用法，前者线程安全，后者不是线程安全，适用于单线程
//                StringBuffer sql = new StringBuffer();
//                sql.append("insert into user(name,age) values(");
//                sql.append(model.getName());
//                sql.append(",");
//                sql.append(model.getAge());
//                sql.append(")");
//                sqls.add(sql.toString());

                String sqlString = "insert into user(name,age) values(?,?)";
                // 使用事务
                pres = connection.prepareStatement("insert into user(name,age) values (?,?)");
                pres.setObject(1, model.getName());
                pres.setObject(2, model.getAge());
                pres.execute();
            }
            // commit
            connection.commit();

            // close
            pres.close();
            statement.close();
            connection.close();

        } catch (Exception e) {
            System.out.println(e.getStackTrace());
            try {
                connection.rollback();
                if (pres != null) {
                    pres.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e1) {
                System.out.println(e1.getStackTrace());
            }
        }
        return true;
    }

删（Delete）

 public static boolean deleteUserInfo(String name) {
    Connection connection = null;
    Statement statement = null;
    try {
        Class.forName(JDBC_DRIVER);
        connection = DriverManager.getConnection(DB_URL, USER, PWD);
        if (connection == null) {
            return false;
        }
        statement = connection.createStatement();
        if (statement == null) {
            return false;
        }
        //boolean success = statement.execute("delete from user where name =" + "'"+name+"'");
        int result = statement.executeUpdate("delete from user where name =" + "'"+name+"'");
        statement.close();
        connection.close();
        return result>0?true:false;

    } catch (Exception e) {
        System.out.println(e.getStackTrace());
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            return false;
        } catch (Exception e1) {
            System.out.println(e1.getStackTrace());
            return false;
        }
    }
}

改（Update）

public static boolean updateUserInfo(TestModel model) {
    Connection connection = null;
    PreparedStatement pres = null;
    try {
        Class.forName(JDBC_DRIVER);
        connection = DriverManager.getConnection(DB_URL,USER,PWD);
        connection.setAutoCommit(false);
        if (connection == null) {
            return false;
        }

        String sql = "update user set user.age=? where user.name=?";
        pres = connection.prepareStatement(sql);
        pres.setObject(1, model.getAge());
        pres.setObject(2, model.getName());

        pres.execute();
        connection.commit();

        pres.close();
        connection.close();
    } catch (Exception e) {
        System.out.println(e.getStackTrace());
        try {
            connection.rollback();
            if (pres != null) {
                pres.close();
            }
            if (connection != null) {
                connection.close();
            }
            System.out.println("更新用户信息失败");
            return false;
        } catch (Exception e1) {
            System.out.println("更新用户信息失败");
            return false;
        }
    }
    return true;
}

查（Query）

public static List<TestModel> getUserInfo() {
    Connection conn = null;
    Statement state = null;
    ArrayList<TestModel> modelArrayList = null;
    try {
        // register jdbc driver
        Class.forName(JDBC_DRIVER);

        // open database
        System.out.println("连接数据库...");
        conn = DriverManager.getConnection(DB_URL, USER, PWD);
        if (conn == null) {
            System.out.println("数据库连接失败");
            return modelArrayList;
        }

        System.out.println("实例化 Statement 对象");
        // execute query sql
        state = conn.createStatement();
        if (state == null) {
            System.out.println("实例化 Statement 对象失败");
        }
        String sqlString = "select name,age from user";
        ResultSet set = state.executeQuery(sqlString);
        // reade data to list
        modelArrayList = new ArrayList<TestModel>();
        while (set.next()) {
            TestModel model = new TestModel();
            model.setName(set.getString("name"));
            model.setAge(set.getInt("age"));
            modelArrayList.add(model);
        }
        set.close();
        state.close();
        conn.close();
    } catch (Exception e) {
        System.out.println(e.getStackTrace());
    } finally {
        try {
            if (state != null) {
                state.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
        }
    }
    System.out.println("执行成功！");
    return modelArrayList;
}

Execute,ExecuteUpdate,ExecuteQuery

三者区别如下：
Execute：执行任务sql，返回boolean值，该值表示是否返回一个ResultSet
ExecuteUpdate：执行增、删、改，返回boolean，该值表示受影响的行数
ExecuteQuery：执行查询，返回ResultSet结果集