package com.shuang3.utils;
import com.shuang.connection.ConnectionTest;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
//读取文件的四个基本信息
public static Connection getConnection() throws Exception {
//获取数据库的链接
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");
//加载驱动
Class.forName(driverClass);
//获取链接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
public 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();
}
}
}
user=root password=7364983day url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT driverClass=com.mysql.cj.jdbc.Driver
修改操作:
public class PreparedStatementUpdateTest {
//增删改
@Test
public void testUptate() {
//1.获取数据库的连接
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句
String sql = "update customers set name =? where id = ?" ;
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1,"哦XX");
ps.setObject(2,20);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//.关闭操作
JDBCUtils.closeResource(conn,ps);
}
}
通
//利用结果集的 元数据 和 反射 完成通用的Customers查询操作
用增删改:
public void update(String sql,Object ...args) {
//获取数据库的连接
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
//预编译sql语句并返回prepareStatement的实例
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 {
JDBCUtils.closeResource(conn,ps);
}
}
针对表进行查询
package com.shuang3.preparedstatement.crud;
import com.shuang3.bean.Custmoer;
import com.shuang3.utils.JDBCUtils;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class CustomerForQuery {
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,5);
//执行,并返回结果集
resultSet = ps.executeQuery();
//处理结果集,判断结果集的下一条是否有数据,如果有数据就返回true并使指针下移
if(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//System.out.println("id = " + id + ",name = " + name + ",email = " + email + " , birth = " +birth );
//Object[] data =new Object[]{id,name,email,birth};
Custmoer custmoer = new Custmoer(id,name,email,birth);
System.out.println(custmoer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
}
}
package com.shuang3.bean; import java.util.Date; public class Custmoer { private int id ; private String name ; private String email; private Date birth; public Custmoer() { } public Custmoer(int id, String name, String email, Date birth) { this.id = id; this.name = name; this.email = email; this.birth = birth; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirth() { return birth; } public void setBirth(Date birth) { this.birth = birth; } @Override public String toString() { return "Custmoer{" + "id=" + id + ", name='" + name + ''' + ", email='" + email + ''' + ", birth=" + birth + '}'; } }public class CustomerForQuery { //利用结果集的 元数据 和 反射 完成通用的Customers查询操作 @Test public void testQueryForCustomers(){ String sql = "select id,name,birth,email from customers where id = ? " ; Custmoer custmoer = queryForCustomers(sql, 13); System.out.println(custmoer); } @Test public Custmoer queryForCustomers(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(); //获取结果集的元数据 ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); if(rs.next()){ Custmoer cust = new Custmoer(); for(int i = 0 ;i < columnCount;i++){ //列的列值 Object columnValue = rs.getObject(i+1); //获取每个列的列名————————————————————————反射 String columnName = rsmd.getColumnName(i + 1); //给cust对象指定的columName属性,赋值为c_Value; Field field = Custmoer.class.getDeclaredField(columnName); field.setAccessible(true); field.set(cust,columnValue); //给cust某个指定的属性赋值为value。 } return cust; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn,ps,rs); } return null; }
package com.shuang3.preparedstatement.crud;
import com.shuang3.bean.Order;
import com.shuang3.utils.JDBCUtils;
import org.testng.annotations.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Date;
public class OrderForQuery {
@Test
public void testOrderForQuery(){
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = orderForQuery(sql, 1);
System.out.println(order);
}
@Test
public Order orderForQuery(String sql ,Object ...args) {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
preparedStatement = conn.prepareStatement(sql);
for(int i = 0 ;i



