介绍

可以通过调用 Connection 对象的 preparedStatement() 方法获取PreparedStatement 对象

PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句

PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，用PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

数据类型转换表

JDBCUtils自定义工具类

因为每次都有重复大量的代码出现，所以我们就封装一个自定义工具类，方便直接调用

/*
操作数据库的工具类*/import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JDBCUtils {/**** @Description 获取数据库的连接* @author KG* @throws Exception*/public static Connection getConnection() throws Exception {// 1.读取配置文件中的4个基本信息InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");Properties pros = new Properties();pros.load(is);String user = pros.getProperty("user");String password = pros.getProperty("password");String url = pros.getProperty("url");String driverClass = pros.getProperty("driverClass");// 2.加载驱动Class.forName(driverClass);// 3.获取连接Connection conn = DriverManager.getConnection(url, user, password);return conn;}/**** @Description 关闭连接和Statement的操作* @author KG*/public static void closeResource(Connection conn, Statement ps){try {if(ps != null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if(conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}/**** @Description 关闭资源操作* @author KG*/public static void closeResource(Connection conn, Statement ps, ResultSet rs) {try {if (ps != null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if (conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}try {if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();}}
}

增


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.Properties;public class jdbcTest {public static void main(String[] args) throws Exception{Connection conn = null;PreparedStatement ps = null;try {// 1.读取配置文件中的4个基本信息InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");Properties pros = new Properties();pros.load(is);String user = pros.getProperty("user");String password = pros.getProperty("password");String url = pros.getProperty("url");String driverClass = pros.getProperty("driverClass");// 2.加载驱动Class.forName(driverClass);// 3.获取连接conn = DriverManager.getConnection(url, user, password);//		System.out.println(conn);//4.预编译sql语句，返回PreparedStatement的实例String sql = "insert into stu01(name,age,id)values(?,?,?)";  //?:占位符 stu01是表名ps = conn.prepareStatement(sql);//5.填充占位符ps.setString(1, "XKK");ps.setInt(2,99);ps.setInt(3,110);//6.执行操作ps.execute();} catch (Exception e) {e.printStackTrace();}finally{//7.资源的关闭try {if(ps != null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if(conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}
}

删

封装了一个增删改的通用方法，可以直接调用，就很方便

import java.sql.Connection;
import java.sql.PreparedStatement;public class jdbcTest {public static void main(String[] args) throws Exception{jdbcTest jt = new jdbcTest();String sql = "delete from stu01 where id = ?";jt.update(sql,110);   //通过查询id来删除//      String sql = "update `order` set order_name = ? where order_id = ?";
//      update(sql,"DD","2");  //适用于当前数据库下所有的表}//通用的 增删改 操作public void update(String sql,Object ...args) {//sql中占位符的个数与可变形参的长度相同！Connection conn = null;PreparedStatement ps = null;try {//1.获取数据库的连接conn = JDBCUtils.getConnection();//2.预编译sql语句，返回PreparedStatement的实例ps = conn.prepareStatement(sql);//3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);//小心参数声明错误！！}//4.执行ps.execute();} catch (Exception e) {e.printStackTrace();} finally {//5.资源的关闭JDBCUtils.closeResource(conn, ps);}}
}

改

这里没有使用上面通用的方法，用了一种直接的方法，使用 JDBCUtils 工具类

import java.sql.Connection;
import java.sql.PreparedStatement;public class jdbcTest {public static void main(String[] args) throws Exception{Connection conn = null;PreparedStatement ps = null;try {// 1.获取数据库的连接conn = JDBCUtils.getConnection();//2.预编译sql语句，返回PreparedStatement的实例String sql = "update stu01 set name = ? where id = ?";ps = conn.prepareStatement(sql);//3.填充占位符ps.setObject(1,"GK");ps.setObject(2,110);  //通过查找id将XKK的姓名修改为GK//4.执行操作ps.execute();} catch (Exception e) {e.printStackTrace();}finally{//7.资源的关闭JDBCUtils.closeResource(conn,ps);}}
}

查

查与其他三类不同，因为查出一条或多条数据是需要返回值的，这里我们就例举几种方法来接受返回值

MySQL BLOB 类型

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;import org.junit.Test;import com.atguigu3.bean.Customer;
import com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;/**使用PreparedStatement实现针对于不同表的通用的查询操作**/
public class PreparedStatementQueryTest {@Testpublic void testGetForList(){String sql = "select id,name,email from customers where id < ?";List<Customer> list = getForList(Customer.class,sql,12);list.forEach(System.out::println);String sql1 = "select order_id orderId,order_name orderName from `order`";List<Order> orderList = getForList(Order.class, sql1);orderList.forEach(System.out::println);}public <T> List<T> getForList(Class<T> clazz,String sql, Object... args){Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();// 获取结果集的元数据 :ResultSetMetaDataResultSetMetaData rsmd = rs.getMetaData();// 通过ResultSetMetaData获取结果集中的列数int columnCount = rsmd.getColumnCount();//创建集合对象ArrayList<T> list = new ArrayList<T>();while (rs.next()) {T t = clazz.newInstance();// 处理结果集一行数据中的每一个列:给t对象指定的属性赋值for (int i = 0; i < columnCount; i++) {// 获取列值Object columValue = rs.getObject(i + 1);// 获取每个列的列名// String columnName = rsmd.getColumnName(i + 1);String columnLabel = rsmd.getColumnLabel(i + 1);// 给t对象指定的columnName属性，赋值为columValue：通过反射Field field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columValue);}list.add(t);}return list;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResource(conn, ps, rs);}return null;}@Testpublic void testGetInstance(){String sql = "select id,name,email from customers where id = ?";Customer customer = getInstance(Customer.class,sql,12);System.out.println(customer);String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";Order order = getInstance(Order.class, sql1, 1);System.out.println(order);}/** 针对于不同的表的通用的查询操作，返回表中的一条记录*/public <T> T getInstance(Class<T> clazz,String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();// 获取结果集的元数据 :ResultSetMetaDataResultSetMetaData rsmd = rs.getMetaData();// 通过ResultSetMetaData获取结果集中的列数int columnCount = rsmd.getColumnCount();if (rs.next()) {T t = clazz.newInstance();// 处理结果集一行数据中的每一个列for (int i = 0; i < columnCount; i++) {// 获取列值Object columValue = rs.getObject(i + 1);// 获取每个列的列名// String columnName = rsmd.getColumnName(i + 1);String columnLabel = rsmd.getColumnLabel(i + 1);// 给t对象指定的columnName属性，赋值为columValue：通过反射Field field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columValue);}return t;}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResource(conn, ps, rs);}return null;}}

查询表中的多组数据


import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;public class jdbcTest {public static void main(String[] args) {jdbcTest jt = new jdbcTest();String sql = "select name,age,id from stu01";List<Student> list = jt.getForList(Student.class,sql);  //查询整个表的所有数据list.forEach(System.out::println);//        String sql = "select name,age,id from stu01 where id < ?";
//        List<Student> list = jt.getForList(Student.class,sql,999);  //将id小于999的数据输出
//        list.forEach(System.out::println);}public <T> List<T> getForList(Class<T> clazz, String sql, Object... args){Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();// 获取结果集的元数据 :ResultSetMetaDataResultSetMetaData rsmd = rs.getMetaData();// 通过ResultSetMetaData获取结果集中的列数int columnCount = rsmd.getColumnCount();//创建集合对象ArrayList<T> list = new ArrayList<T>();while (rs.next()) {T t = clazz.newInstance();// 处理结果集一行数据中的每一个列:给t对象指定的属性赋值for (int i = 0; i < columnCount; i++) {// 获取列值Object columValue = rs.getObject(i + 1);// 获取每个列的列名// String columnName = rsmd.getColumnName(i + 1);String columnLabel = rsmd.getColumnLabel(i + 1);// 给t对象指定的columnName属性，赋值为columValue：通过反射Field field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columValue);}list.add(t);}return list;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResource(conn, ps, rs);}return null;}
}