vlambda博客
学习文章列表

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

JAVA操作数据库CURD——MySQL篇


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

JAVA操作数据库CURD——MySQL篇


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<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;}

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

Execute,ExecuteUpdate,ExecuteQuery

三者区别如下:

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

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

ExecuteQuery:执行查询,返回ResultSet结果集


end