目录
一、配置文件
二、工具类
三、JavaBean
四、通用增删改
五、通用查询
六、preparedStatement的优势
一、配置文件
user=root
password=******
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver
注 : password是DBMS(数据库管理系统)中设置的密码
二、工具类的编写(实现数据库的开启与关闭功能)
package CSDN_jdbc;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @ClassName 操作数据库的工具类
* @Author 一熹
* @Version 1.8
**/
public class crudUtil {
/**
* @description: 获取数据库的连接
* @param: void
* @return: java.sql.Connection
* @author: 一熹
*/
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;
}
/**
* @description: 关闭数据库的连接
* @param: conn, ps, rs
* @return: void
* @author: 一熹
*/
public static void closeResource(Connection conn, PreparedStatement ps) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//方法的重载
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三、JavaBean的编写(以表Customer与Order为例)
返回目录
下图是Customer的JavaBean
package CSDN_jdbc.JavaBean;
import java.sql.Date;
/**
* @ClassName Customer的JavaBean
* @Author 一熹
* @Version 1.8
**/
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
}
public Customer(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 "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
}
下图是Order的JavaBean
package CSDN_jdbc.JavaBean;
import java.sql.Date;
/**
* @ClassName Order的JavaBean
* @Author 一熹
* @Version 1.8
**/
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
}
public Order(int orderId, String orderName, Date orderDate) {
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}
四、通用增删改操作方法以及测试
/**
* @ClassName 通用增删改操作方法
* @Author 一熹
* @Version 1.8
**/
public class preparedStatementTest {
public void update(String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = crudUtil.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
crudUtil.closeResource(conn, ps);
}
}
/**
* @description: 对通用增删改方法的测试
* @param: void
* @return: void
* @author: 一熹
*/
@Test
public void test1() {
String sql = "update customers set name = ? where id = ?";//预编译,'?'代表占位符
update(sql, "周润发", 19);
}
五、通用查询方法以及测试
/**
* @description: 通用查询操作
* @param: clazz,sql,args
* @return: java.util.List<T>
* @author: 一熹
*/
public <T> List<T> query(Class<T> clazz, String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<T> list = null;
try {
conn = crudUtil.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);//可变形参可看作数组
}
rs = ps.executeQuery();
list = new ArrayList<>();
while (rs.next()) {//next()作用:判断结果集的下一条是否有数据,则返回true并下移指针,否则返回false指针不动
ResultSetMetaData rsmd = ps.getMetaData();//获取结果集的元数据
int columnCount = rsmd.getColumnCount();//获取列数
T t = clazz.newInstance();//通过反射常见对象
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);//获取列的别名
//通过反射给columnLable列赋值为columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
crudUtil.closeResource(conn, ps, rs);
}
return null;
}
/**
* @description:对通用查询方法的测试
* @param: void
* @return: void
* @author: 一熹
*/
@Test
public void testQuery(){
//别名用于和JavaBean的属性同名,由于order是关键词故用着重号(`)修饰
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
List<Order> query = query(Order.class, sql, 5);
query.forEach(System.out::println);
}
}
六、preparedStatement相较于statement的优势
返回目录
1.解决了statement的拼串问题
2.避免了SQL注入问题
3.可以操作Blob数据(占位符中可以传入流)
4.更高效的批量操作(预编译,只需校验一次)