Java操作MySQL，创建JDBC工具类、使用Druid连接池技术，实现CRUD(增、删、改、查)

要求&＃xff1a;使用JDBC创建一张表&＃xff0c;表名student&＃xff0c;字段包含id&＃xff0c;姓名(username)、班级(class_and_grade)学号(student_number)、性别(sex)、年龄(age)、邮箱(email)。并且添加相应的约束。对表实现CRUD(注意增删改得使用。

1.创建一个module命名JDBC
2.添加资源druid.proporties

3.和MySQL数据库取得连接


4.配置JDBC工具类
package com.cn.zpark.utils;

import java.sql.*;

public class JDBCUtils {
// 定义数据路连接信息
private static String url &＃61; “jdbc:mysql://localhost:3306/my_druid?useSSL&＃61;false&serverTimezone&＃61;UTC&characterEncoding&＃61;UTF-8”;
private static String user &＃61; “root”;
private static String pwd &＃61; “root”;

public static Connection getConn() throws Exception {// 注册驱动Class.forName("com.mysql.jdbc.Driver");// 返回连接对象return DriverManager.getConnection(url, user, pwd);
}public static void close(Connection conn, ResultSet res, Statement... stat){try {if(res !&＃61; null){res.close();}for (Statement statement : stat) {if(statement !&＃61; null){statement.close();}}assert conn !&＃61; null;conn.close();} catch (SQLException e) {e.printStackTrace();}
}


}

5.开始创建表student
代码&＃xff1a;
package com.cn.zpark;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCCreateTable {
public static void main(String[] args) {
// 定义连接数据库信息
String url &＃61; “jdbc:mysql://localhost:3306/my_druid?useSSL&＃61;false&serverTimezone&＃61;UTC&characterEncoding&＃61;UTF-8”;
String user &＃61; “root”;
String pwd &＃61; “root”;
Connection conn &＃61; null;
Statement stat &＃61; null;

try {// 1、注册驱动Class.forName("com.mysql.jdbc.Driver");// 2、获取连接对象conn &＃61; DriverManager.getConnection(url, user, pwd);// 3、获取statement对象stat &＃61; conn.createStatement();// 4、创建sql语句String sql &＃61; "create table student(id int primary key auto_increment, username varchar(100) not null unique," &＃43;"class_and_grade varchar(100) not null , student_number varchar(100) not null , sex varchar(10) not null, age varchar (10) not null, email varchar(100) not null unique )";// 5、执行sqlstat.execute(sql);System.out.println("创建表成功");} catch(Exception e) {e.printStackTrace();System.out.println("创建表失败&＃xff0c;原因未知");} finally {// 6、关闭资源if(stat !&＃61; null){try {stat.close();} catch (SQLException e) {e.printStackTrace();}}if(conn !&＃61; null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}


}

创建成功后在mysql数据库中可以查看表结构

6插入数据&＃xff1a;
package com.cn.zpark;

import com.cn.zpark.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCInsert {
public static void main(String[] args) {
Connection conn &＃61; null;
PreparedStatement stat &＃61; null;
try {
conn &＃61; JDBCUtils.getConn();
conn.setAutoCommit(false);
String sql &＃61; “insert into student(username,class_and_grade,student_number,sex,age,email)values” &＃43;
“(‘TOM’,‘计本一班’,‘31’,‘男’,‘18’,‘tm123456&＃64;qq.com’),” &＃43;
“(‘王五’,‘计本一班’,‘32’,‘男’,‘19’,‘ww123456&＃64;qq.com’),” &＃43;
“(‘小明’,‘计本二班’,‘23’,‘女’,‘20’,‘xm123456&＃64;qq.com’),” &＃43;
“(‘张三’,‘计本一班’,‘42’,‘女’,‘21’,‘zs123456&＃64;qq.com’),” &＃43;
“(‘钱七’,‘计本二班’,‘15’,‘男’,‘19’,‘qq123456&＃64;qq.com’)”;
stat &＃61; conn.prepareStatement(sql);
stat.executeUpdate();
conn.commit();
System.out.println(“成功插入数据”);
} catch (Exception e) {
e.printStackTrace();
try {
if (conn !&＃61; null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.close(conn, null, stat);
}
}
}

7.删除数据
package com.cn.zpark;

import com.cn.zpark.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCDelete {
public static void main(String[] args) {
Connection conn &＃61; null;
PreparedStatement stat &＃61; null;
try {
conn &＃61; JDBCUtils.getConn();
// 开启事务&＃xff0c;关闭事务字段提交
conn.setAutoCommit(false);
String sql &＃61; “delete from student where username &＃61;‘张三’”;
// 创建预编译sql对象
stat &＃61; conn.prepareStatement(sql);
stat.executeUpdate();
// 提交事务
conn.commit();
} catch(Exception e) {
e.printStackTrace();
// 回滚事务
try {
if(conn !&＃61; null){
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.close(conn, null, stat);
}
}
}

8.修改数据&＃xff1a;
package com.cn.zpark;

import com.cn.zpark.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCUpdate {
public static void main(String[] args) {
Connection conn &＃61; null;
PreparedStatement stat1 &＃61; null;
try {
conn &＃61; JDBCUtils.getConn();
// 开启事务&＃xff0c;关闭事务字段提交
conn.setAutoCommit(false);
String sql1 &＃61; “update student set class_and_grade&＃61; ‘计本一班’ where student_number &＃61; ‘23’ or student_number &＃61; ‘15’”;
// 创建预编译sql对象
stat1 &＃61; conn.prepareStatement(sql1);
stat1.executeUpdate();
// 提交事务
conn.commit();
System.out.println(“修改数据成功&＃xff01;”);
} catch(Exception e) {
e.printStackTrace();
// 回滚事务
try {
if(conn !&＃61; null){
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.close(conn, null, stat1);
}
}
}

9.查询数据&＃xff1a;
package com.cn.zpark;

import com.cn.zpark.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCSelect {
public static void main(String[] args) {
Connection conn &＃61; null;
PreparedStatement stat &＃61; null;
ResultSet res &＃61; null;
try {
conn &＃61; JDBCUtils.getConn();
String sql &＃61; “select id,username,class_and_grade,student_number,sex,age,email from student where username&＃61;‘TOM’ or username&＃61;‘小明’”;
// 创建预编译sql对象
stat &＃61; conn.prepareStatement(sql);
res &＃61; stat.executeQuery();
// 变量结果集
while (res.next()) {
System.out.println(res.getInt(“id”));
System.out.println(res.getString(“username”));
System.out.println(res.getString(“class_and_grade”));
System.out.println(res.getString(“student_number”));
System.out.println(res.getString(“sex”));
System.out.println(res.getString(“age”));
System.out.println(res.getString(“email”));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn, res, stat);
}
}
}