1. 使用PreparedStatement实现通用查询操作
public class TongyongQuery {
public static void main(String[] args) {
/*String sql = "SELECT order_id id, order_name name, order_date date FROM `order` WHERE order_id = ?";
order instance = getInstance(order.class, sql, 2);
System.out.println(instance);*/
String sql = "SELECT id,name,email,birth FROM customers where id = ?";
Customer instance = getInstance(Customer.class, sql, 5);
System.out.println(instance);
}
public static <T> T getInstance(Class<T> clazz,String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCutils.huoqulianjie();
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 t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每个列的列值,通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名,通过ResultSetMetaData
//getColumnName();获取列名
//rsmd.getColumnLabel()获取列的别名
// String columnName = rsmd.getColumnName(i + 1); 不推荐使用
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射,将指定名的属性赋值为指定值columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCutils.closeziyuan(conn, ps, rs);
}
return null;
}
}
2.使用PreparedStatement实现通用多行查询操作
public class TongyongduoQuery {
public static void main(String[] args) {
String sql = "SELECT name,id,email FROM customers ";
ArrayList<Customer> instance = getInstance(Customer.class, sql);
instance.forEach(System.out::println);
}
public static <T> ArrayList<T> getInstance(Class<T> clazz, String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCutils.huoqulianjie();
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();
ArrayList<T> list = new ArrayList<>();
while(rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取每个列的列值,通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名,通过ResultSetMetaData
//getColumnName();获取列名
//rsmd.getColumnLabel()获取列的别名
// String columnName = rsmd.getColumnName(i + 1); 不推荐使用
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射,将指定名的属性赋值为指定值columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCutils.closeziyuan(conn, ps, rs);
}
return null;
}
}
3. JDBC API小结
两种思想
-
面向接口编程的思想
-
ORM思想(object relational mapping)
- 一个数据表对应一个java类
- 表中的一条记录对应java类的一个对象
- 表中的一个字段对应java类的一个属性
sql是需要结合列名和表的属性名来写。注意起别名
- 两种技术
- JDBC结果集的元数据:ResultSetMetaData
- 获取列数:getColumnCount()
- 获取列的别名:getColumnLabel()
- 通过反射,创建指定类的对象,获取指定的属性并赋值
4.使用PreparedStatement实现高效批量插入
public void testInsert2() throws Exception{
long start = System.currentTimeMillis();
Connection conn = JDBCUtils.getConnection();
//1.设置为不自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 1000000;i++){
ps.setString(1, "name_" + i);
//1.“攒”sql
ps.addBatch();
if(i % 500 == 0){
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
//2.提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));//1000000条:4978
JDBCUtils.closeResource(conn, ps);
}