java mysql curd_JAVA操作数据库CURD——MySQL篇

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

4 点击Apply，此时已经成功连接上jdbc驱动了，接下来即可操作mysql

数据库操作

数据库连接信息：

/*** 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=UTCstatic 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 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;}// 设置自动提交 falseconnection.setAutoCommit(false);ArrayList sqls = new ArrayList();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();}// commitconnection.commit();// closepres.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 getUserInfo() {Connection conn = null;Statement state = null;ArrayList modelArrayList = null;try {// register jdbc driverClass.forName(JDBC_DRIVER);// open databaseSystem.out.println("连接数据库...");conn = DriverManager.getConnection(DB_URL, USER, PWD);if (conn == null) {System.out.println("数据库连接失败");return modelArrayList;}System.out.println("实例化 Statement 对象");// execute query sqlstate = conn.createStatement();if (state == null) {System.out.println("实例化 Statement 对象失败");}String sqlString = "select name,age from user";ResultSet set = state.executeQuery(sqlString);// reade data to listmodelArrayList = new ArrayList();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;}

最后说明一下下面3中方法的区别：

Execute,ExecuteUpdate,ExecuteQuery

三者区别如下：

Execute：执行任务sql，返回boolean值，该值表示是否返回一个ResultSet

ExecuteUpdate：执行增、删、改，返回boolean，该值表示受影响的行数

ExecuteQuery：执行查询，返回ResultSet结果集

end