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

要求：使用JDBC创建一张表，表名student，字段包含id，姓名(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 = “jdbc:mysql://localhost:3306/my_druid?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8”;
private static String user = “root”;
private static String pwd = “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 != null){
            res.close();
        }
        for (Statement statement : stat) {
            if(statement != null){
                statement.close();
            }
        }
        assert conn != null;
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

5.开始创建表student
代码：
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 = “jdbc:mysql://localhost:3306/my_druid?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8”;
String user = “root”;
String pwd = “root”;
Connection conn = null;
Statement stat = null;

    try {
        // 1、注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2、获取连接对象
        conn = DriverManager.getConnection(url, user, pwd);
        // 3、获取statement对象
        stat = conn.createStatement();
        // 4、创建sql语句
        String sql = "create table student(id int primary key auto_increment, username varchar(100) not null unique," +
                "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、执行sql
        stat.execute(sql);

        System.out.println("创建表成功");
    } catch(Exception e) {
        e.printStackTrace();
        System.out.println("创建表失败，原因未知");
    } finally {
        // 6、关闭资源
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

}

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

6插入数据：
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 = null;
PreparedStatement stat = null;
try {
conn = JDBCUtils.getConn();
conn.setAutoCommit(false);
String sql = “insert into student(username,class_and_grade,student_number,sex,age,email)values” +
“(‘TOM’,‘计本一班’,‘31’,‘男’,‘18’,‘tm123456@qq.com’),” +
“(‘王五’,‘计本一班’,‘32’,‘男’,‘19’,‘ww123456@qq.com’),” +
“(‘小明’,‘计本二班’,‘23’,‘女’,‘20’,‘xm123456@qq.com’),” +
“(‘张三’,‘计本一班’,‘42’,‘女’,‘21’,‘zs123456@qq.com’),” +
“(‘钱七’,‘计本二班’,‘15’,‘男’,‘19’,‘qq123456@qq.com’)”;
stat = conn.prepareStatement(sql);
stat.executeUpdate();
conn.commit();
System.out.println(“成功插入数据”);
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != 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 = null;
PreparedStatement stat = null;
try {
conn = JDBCUtils.getConn();
// 开启事务，关闭事务字段提交
conn.setAutoCommit(false);
String sql = “delete from student where username =‘张三’”;
// 创建预编译sql对象
stat = conn.prepareStatement(sql);
stat.executeUpdate();
// 提交事务
conn.commit();
} catch(Exception e) {
e.printStackTrace();
// 回滚事务
try {
if(conn != null){
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.close(conn, null, stat);
}
}
}

8.修改数据：
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 = null;
PreparedStatement stat1 = null;
try {
conn = JDBCUtils.getConn();
// 开启事务，关闭事务字段提交
conn.setAutoCommit(false);
String sql1 = “update student set class_and_grade= ‘计本一班’ where student_number = ‘23’ or student_number = ‘15’”;
// 创建预编译sql对象
stat1 = conn.prepareStatement(sql1);
stat1.executeUpdate();
// 提交事务
conn.commit();
System.out.println(“修改数据成功！”);
} catch(Exception e) {
e.printStackTrace();
// 回滚事务
try {
if(conn != null){
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.close(conn, null, stat1);
}
}
}

9.查询数据：
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 = null;
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = JDBCUtils.getConn();
String sql = “select id,username,class_and_grade,student_number,sex,age,email from student where username=‘TOM’ or username=‘小明’”;
// 创建预编译sql对象
stat = conn.prepareStatement(sql);
res = 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);
}
}
}