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=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;
}
最后说明一下下面3中方法的区别:
Execute,ExecuteUpdate,ExecuteQuery
三者区别如下:
Execute:执行任务sql,返回boolean值,该值表示是否返回一个ResultSet
ExecuteUpdate:执行增、删、改,返回boolean,该值表示受影响的行数
ExecuteQuery:执行查询,返回ResultSet结果集
end