JDBC实现增删改操作(PreparedStatement)

/**
* jdbc.properties
*/
user=root
password=root
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

/**
* JDBCUtil.java
*/
package util;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类
 */
public class JDBCUtil {
    /**
     * 获取数据库连接
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            //1.读取配置文件
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            String name = pros.getProperty("name", "root");
            String password = pros.getProperty("password", "root");
            String url = pros.getProperty("url", "jdbc:mysql://localhost:3306/test");
            String driverClass = pros.getProperty("driverClass", "com.mysql.cj.jdbc.Driver");
            //2.加载驱动
            Class.forName(driverClass);
            //3.获取连接
            connection = DriverManager.getConnection(url, name, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 释放资源
     *
     * @param conn
     * @param ps
     */
    public static void closeResource(Connection conn, PreparedStatement ps) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 释放资源
     *
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

/**
* PreparedStatementTest.java
*/
import org.junit.Test;
import util.JDBCUtil;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

/**
 * 使用PreparedStatement实现增删改操作
 */
public class PreparedStatementTest {


    /**
     * 增加操作
     */
    @Test
    public void insertTest() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.读取配置文件
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            String name = pros.getProperty("name", "root");
            String password = pros.getProperty("password", "root");
            String url = pros.getProperty("url", "jdbc:mysql://localhost:3306/test");
            String driverClass = pros.getProperty("driverClass", "com.mysql.cj.jdbc.Driver");
            //2.加载驱动
            Class.forName(driverClass);
            //3.获取连接
            conn = DriverManager.getConnection(url, name, password);
            //4.预编译sql语句,返货PrepareStatement的实例
            String sql = "insert into user (name,age,birth) values (?,?,?)";//?是占位符
            ps = conn.prepareStatement(sql);
            //5.填充占位符
            ps.setString(1, "hero");
            ps.setInt(2, 18);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("1995-12-31");
            ps.setDate(3, new java.sql.Date(date.getTime()));
            //6.执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 通用的增删改操作
     *
     * @param sql
     * @param args
     */
    public void updateCommon(String sql, Object... args) {//可变形参
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            conn = JDBCUtil.getConnection();
            //2.预编译sql语句,返货PrepareStatement的实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //4.执行操作
            //ps.execute();
            ps.executeUpdate();//返回执行成功的行数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtil.closeResource(conn, ps);
        }
    }

    @Test
    public void updateTest() {
        String sql = "update user set name=? where id=?";
        updateCommon(sql, "王思聪", 1);
    }
}