使用下面的数据库操作工具类`JDBCUtil`完成数据库简单的增删改查

最新推荐文章于 2024-07-28 19:10:22 发布

m0_67872714

最新推荐文章于 2024-07-28 19:10:22 发布

阅读量194

点赞数

文章标签：数据库 java mysql

本文链接：https://blog.csdn.net/m0_67872714/article/details/125817580

版权

```java
public class JDBCUtil {
    // MySQL 5应为com.mysql.cj.jdbc.Driver
    private static final String driver = "com.mysql.cj.jdbc.Driver";
    private static final String url = "jdbc:mysql://localhost:3306/XXX?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8";
    private static final String userName = "root";
    private static final String password = "123456";

    // 获取数据库连接
    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, userName, password);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return con;
    }

    // 数据库查询，返回结果集
    public static ResultSet query(Connection con, PreparedStatement st, ResultSet rs, String sql
            , Object[] params) throws SQLException {
        st = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                st.setObject(i + 1, params[i]);
            }
        }
        rs = st.executeQuery();
        return rs;
    }

    // 数据库增删改
    public static int update(Connection con, String sql
            , Object[] params, ResultSet rs, PreparedStatement st) throws SQLException {
        st = con.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            st.setObject(i + 1, params[i]);
        }
        return st.executeUpdate();
    }

    // 关闭数据库连接
    public static void release(Connection con, Statement st, ResultSet rs) {
        boolean flag = true;

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

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

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

1、连接数据库

public class DBUtil {
public static Connection getConnection()
{
try {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc_test?useSSL=true&characterEncoding=utf-8" +
"&user=root&password=12345678");
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet resultSet, PreparedStatement statement,Connection connection)
{
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

2. select操作（查询操作）

public void select()
{
ResultSet resultSet=null;
PreparedStatement statement=null;
Connection connection=null;
try {
connection= DBUtil.getConnection();
System.out.println("创建连接成功");
//写sql
String sql="select * from userinfo";
//得到statement对象
statement=connection.prepareStatement(sql);
//执行sql，得到结果集
resultSet=statement.executeQuery();
//处理结果集
while(resultSet.next())
{
System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.close(resultSet,statement,connection);
}
}

3. insert操作（添加操作）

public void insert()
{
ResultSet resultSet = null;
PreparedStatement statement = null;
Connection connection = null;
try {
connection= DBUtil.getConnection();
System.out.println("创建连接成功");
//3、写sql
String sql="insert into userinfo(username,password) values (?,?)";
//4、得到statement对象
statement=connection.prepareStatement(sql);
statement.setString(1,"王五");
statement.setString(2,"22313");
//5、执行sql
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//7、关闭资源
DBUtil.close(null,statement,connection);
}
}

4. update操作（修改操作）

public void update()
{
ResultSet resultSet = null;
PreparedStatement statement = null;
Connection connection = null;
String name,password;
Scanner scan=new Scanner(System.in);
System.out.println("请输入要修改的用户的用户名：");
name=scan.next();
System.out.println("请输入要修改的密码：");
password=scan.next();
try {
connection = DBUtil.getConnection();
//3、写sql
String sql="update userinfo set password=? where username=?";
statement=connection.prepareStatement(sql);
statement.setString(1,password);
statement.setString(2,name);
statement.executeUpdate();
System.out.println("修改成功");
}catch(Exception e){
e.printStackTrace();
}finally {
DBUtil.close(null,statement,connection);
}
}

5. delete操作（删除操作）

public void delete()
{
ResultSet resultSet = null;
PreparedStatement statement = null;
Connection connection = null;
String name;
Scanner scan=new Scanner(System.in);
System.out.println("请输入要删除的用户的用户名：");
name=scan.next();
try {
connection = DBUtil.getConnection();
String sql="delete from userinfo where username=?";
statement=connection.prepareStatement(sql);
statement.setString(1,name);
statement.executeUpdate();
System.out.println("删除成功");
}catch (Exception e){
e.printStackTrace();
}finally {
DBUtil.close(null,statement,connection);
}
}