81_Java_JDBC2_通用
【摘要】 通用操作
1 通用创建连接 & 关闭
public class JDBCUtils {
// 静态操作，留给调用者去处理异常
public static Connection getConnection() throws Exception {
InputStream ins = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties ps = new Properties();
ps.load(ins);
String user = ps.getProperty("user");
String password = ps.getProperty("password");
String url = ps.getProperty("url");
String driverClass = ps.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
public static void closeResource(Connection con, Statement ps){
try {
if (con !=null)
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (ps !=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
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();
}
}
}2 通用 增删改
public void update(String sql, Object ...args ) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1 创建连接
conn = JDBCUtils.getConnection();
//预编译SQL，返回preparestatement的实例
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) { // 参数用于填充占位符，几个参数就有几个参数
ps.setObject(i+1, args[i]); // 填充占位符
}
ps.execute();
int i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
@Test
public void test3() throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date d1 = sdf.parse("2000-10-01");
java.sql.Date date1 = new java.sql.Date(d1.getTime());
String sql = "insert into `order`(order_name, order_date) values (?,?)";
update(sql, "DD", date1 );
}
}普通表查询_预编译_结果集_构造器创建对象
@Test
public void test0() throws Exception {
Connection conn = JDBCUtils.getConnection();
String sql = "select id, name, email , birth from customers where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
ResultSet rs = ps.executeQuery();
if (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date date = rs.getDate(4);
Customer customer = new Customer(id, name, email, date);
System.out.println(customer);
}
JDBCUtils.closeResource(conn,ps);
}
}普通表查询升级_预编译_结果集_反射创建对象_赋值_返回对象
public class CustomerForQuery {
public Customer queryForCustomers(String sql, Object ...args) throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
// 1 预编译sql 填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
ResultSet rs = ps.executeQuery();
// 2 resultset结果集 将列名/列数 封装在 rs的元数据里面
ResultSetMetaData metaData = rs.getMetaData();
// 3 结果集中的列的个数
int count = metaData.getColumnCount();
if (rs.next()){
// 创建对象
Customer customer = new Customer();
//4 循环取出 列名 SQL(index 1 开始)
for (int i = 0; i < count ; i++) {
Object columnValue = rs.getObject(i + 1);
// 获取列的列名，既表的列名，如果SQL里含有别名=对应对象的属性， 可以用getColumnLabel
String columnLabel = metaData.getColumnLabel(i + 1);
String columnName = metaData.getColumnName(i + 1);
// String columnLabel = metaData.getColumnLabel(i + 1);
// 5 通过反射 动态拿到实例的属性 + 赋值（对象 ， 值）
// 调用运行时类的指定属性
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(customer,columnValue);
}
return customer;
}
JDBCUtils.closeResource(conn, ps ,rs);
return null;
}
@Test
public void test1() {
String sql = "select id,name,birth,email from customers where id = ?";
try {
Customer customer = queryForCustomers(sql, 1);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
}
}
}
针对不同表的通用操作_返回一条记录_返回多条记录
public class QueryTest {
@Test
public void testQuery1() throws Exception {
String sql = "SELECT id, name, email, birth from customers where id = ?";
// Customer customer = psQuery1(Customer.class, sql, 3);
// System.out.println(customer);
String sql2 = "SELECT id, name, email, birth from customers where id > ?";
List<Customer> customers = psQuery2(Customer.class, sql2, 4);
System.out.println(customers);
}
//使用PreparedStatement实现针对于不同表的通用的查询操作_返回一条记录
public <T> T psQuery1(Class<T> clazz, java.lang.String sql, Object... args ) throws Exception {
Connection conn = JDBCUTILS.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1, args[i]);
}
ResultSet rs = ps.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData metaData = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int count = metaData.getColumnCount();
if (rs.next()){
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for (int i = 0; i <count ; i++) {
// 获取列名
String columnLabel = metaData.getColumnLabel(i + 1);
// 获取列值
Object columnValue = rs.getObject(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
JDBCUTILS.closeResource(conn,ps,rs);
return null;
}
//使用PreparedStatement实现针对于不同表的通用的查询操作_返回多条记录
public <T> List<T> psQuery2(Class<T> clazz, String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<T> list = 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 metaData = rs.getMetaData();
int count = metaData.getColumnCount();
list = new ArrayList<>();
while (rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < count ; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUTILS.closeResource(conn,ps, rs);
}
return null;
}
}
批量插入
/*
* 使用PreparedStatement实现批量数据的操作
*
* update、delete本身就具有批量操作的效果。
* 此时的批量操作，主要指的是批量插入。使用PreparedStatement如何实现更高效的批量插入？
*
* 题目：向goods表中插入20000条数据
* CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25)
);
* 方式一：使用Statement
* Connection conn = JDBCUtils.getConnection();
* Statement st = conn.createStatement();
* for(int i = 1;i <= 20000;i++){
* String sql = "insert into goods(name)values('name_" + i + "')";
* st.execute(sql);
* }
*
*/
public class InsertTest {
//批量插入的方式二：使用PreparedStatement
@Test
public void testInsert1() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for(int i = 1;i <= 20000;i++){
ps.setObject(1, "name_" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//20000:83065
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.closeResource(conn, ps);
}
}
/*
* 批量插入的方式三：
* 1.addBatch()、executeBatch()、clearBatch()
* 2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
* ?rewriteBatchedStatements=true 写在配置文件的url后面
* 3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
*/
@Test
public void testInsert2() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for(int i = 1;i <= 1000000;i++){
ps.setObject(1, "name_" + i);
//1."攒"sql
ps.addBatch();
if(i % 500 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//20000:83065 -- 565
} catch (Exception e) { //1000000:16086
e.printStackTrace();
}finally{
JDBCUtils.closeResource(conn, ps);
}
}
//批量插入的方式四：设置连接不允许自动提交数据
@Test
public void testInsert3() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
//设置不允许自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for(int i = 1;i <= 1000000;i++){
ps.setObject(1, "name_" + i);
//1."攒"sql
ps.addBatch();
if(i % 500 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
//提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//20000:83065 -- 565
} catch (Exception e) { //1000000:16086 -- 5114
e.printStackTrace();
}finally{
JDBCUtils.closeResource(conn, ps);
}
}
}
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）