public void test1(){
//1. 获取连接
Connection conn = null;
PreparedStatement ps = null;
//4. 执行 SQL(获取 ResultSet ： 结果集)
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2. 获取 PreparedStatement，用于发送 SQL
String sql = "select id, name, email, birth from customers where id = ?";
ps = conn.prepareStatement(sql);
//3. 填充占位符
ps.setInt(1, 16);
rs = ps.executeQuery();
//5. 获取结果集数据
if(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
System.out.println(id + "," + name + "," + email + "," + birth);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭连接
JDBCUtils.close(rs, ps, conn);
}
}
/*
* ORM （Object Relateional Mapping） : 对象关系映射
*
* 数据表中一张表 ---- Java 一个类
* 数据表中一个字段 ---- Java 一个属性
* 数据表中一条数据 ---- Java 一个对象
*
*/
//针对 Order 表写一个查询一个 Order 对象并返回的方法，以及一堆 Order 对象并返回的方法
@Test
public void test2(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id, name, email, birth from customers where id < ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 16);
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, ps, conn);
}
}
@Test
public void test4(){
String sql = "select id, name, email, birth from customers where id = ?";
Customer cust = get2(sql, Customer.class, 10);
System.out.println(cust);
System.out.println("-----------------------------------------------");
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = get2(sql2, Order.class, 2);
System.out.println(order);
}
public <T> T get2(String sql, Class<T> clazz, Object ... args){//get2(sql, Order.class)
T t = null;
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();
//获取当前结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()){
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnName = rsmd.getColumnLabel(i+1);
//根据列名获取对应列的数据
Object columnValue = rs.getObject(columnName);
Field field = clazz.getDeclaredField(columnName);//注意：必须保证结果集中列名（别名），与属性名称保持一致！！！！！
field.setAccessible(true);//忽略访问权限
field.set(t, columnValue);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, ps, conn);
}
return t;
}
/*
* **通用查询：
*
* 1. 需要返回一个什么类型的对象，不能确定？
* 泛型：确定返回值类型
* 反射：确定返回的对象
*
* 2. 对于结果集的处理，不能确定？
* ResultSetMetaData : 结果集的元数据
* getColumnCount() : 返回结果集的列数
* getColumnName() : 返回结果集的列名
* getColumnLabel() : 返回结果集列的别名（有别名获取别名，没别名获取列名）
*/
@Test
public void test5(){
String sql = "select id, name, email, birth from customers where id < ?";
List<Customer> list = getList(sql, Customer.class, 16);
for (Customer customer : list) {
System.out.println(customer);
}
System.out.println("----------------------------------------------------");
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id < ?";
List<Order> list2 = getList(sql2, Order.class, 5);
for (Order order : list2) {
System.out.println(order);
}
}
public <T> List<T> getList(String sql, Class<T> clazz, Object... args) {
List<T> list = new ArrayList<>();
// 1. 获取连接
Connection conn = null;
// 2. 获取 PreparedStatement， 用于发送 SQL
PreparedStatement ps = null;
// 4. 执行 SQL, 获取 ResultSet 结果集
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
// 3. 根据可变参数填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 5. 获取当前结果集的元数据 ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 6. 获取结果集的列数
int columnCount = rsmd.getColumnCount();
// 7. 获取结果集中数据
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 7.1 根据元数据获取列名
String columnName = rsmd.getColumnLabel(i + 1);
// 7.2 根据列名获取对应列的值
Object columnValue = rs.getObject(columnName);
// 7.3 将获取的列值封装进对象
PropertyUtils.setProperty(t, columnName, columnValue);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 8. 关闭连接
JDBCUtils.close(rs, ps, conn);
}
return list;
}
注意：上边 代码test5 中使用了为ResultSetMetaData配置对象的属性的工具类： PropertyUtils.setProperty(t, columnName, columnValue);
加载工具类如下：
将以上两个.jar文件拷贝到项目中，并build path，然后就可以使用啦
@Test
public void test1(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into emp values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i+1);
ps.setString(2, "emp_" + i);
//执行 SQL
ps.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
}
//批量处理
@Test
public void test2(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into emp values(?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i+1);
ps.setString(2, "emp_" + i);
//积攒 SQL
ps.addBatch();
if((i+1) % 1000 == 0){
//执行 SQL
ps.executeBatch();
//清空 SQL
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, ps, conn);
}
}
以上两段程序都是向数据库中插入100000条数据，但执行效率却天壤之别，原因是代码段二使用了批处理
注意：开启批处理的方法是使用DriverManager.getConnection(url, user, password);加载url 的时候将url修改为：jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true就可以啦
