JDBC 学习笔记 day02
PreparedStatement
此处的多种方式仍然采用迭代形式给出,了解优化过程是有意义的。
相比于 Statement 的好处
- 解决了 Statement 的 SQL 注入问题
- PreparedStatement 能操作 Blob 类型的数据
- 可以实现更高效的批量操作
PreparedStatement 实现 增,删,改 操作
简介:增,删,改操作的共性就是不需要数据库返回数据,故归结为一类操作。
@Test
//方式一:实现增加操作
public void testInsert() {
Connection con = null;
PreparedStatement ps = null;
try {
//1.读取配置文件的四个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("data.properties");
Properties pro = new Properties();
pro.load(is);
String url = pro.getProperty("url");
String className = pro.getProperty("className");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//2.注册驱动
Class.forName(className);
//3.获取连接
con = DriverManager.getConnection(url, user, password);
//4.预编译 sql 语句,返回 PreparedStatement 实例
String sql = "insert into customers(name, email, birth) values(?, ?, ?)";
ps = con.prepareStatement(sql);
//5.填充占位符
ps.setString(1,"哪吒");
ps.setString(2,"nezha@126.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parse = simpleDateFormat.parse("2000-10-10");
ps.setDate(3,new java.sql.Date(parse.getTime()));
//6.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//7.关闭资源
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Test
//通过编写 JDBCUtils 工具类,将重复操作封装在其中,实现删除操作
public void testDelete() {
Connection con = null;
PreparedStatement ps = null;
try {
//1.获取连接
con = JDBCUtils.createConnection();
//2.预编译 sql 语句,返回 PreparedStatement 实例
String sql = "delete from customers where id = ?";
ps = con.prepareStatement(sql);
//3.填充占位符
ps.setInt(1, 20);
//4.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JDBCUtils.closeSource(con, ps);
}
}
javaUtils类的编写
public class JDBCUtils {
public static Connection createConnection() throws Exception {
//1.读取配置文件的四个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("data.properties");
Properties pro = new Properties();
pro.load(is);
String url = pro.getProperty("url");
String className = pro.getProperty("className");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//2.注册驱动
Class.forName(className);
//3.获取连接
Connection con = DriverManager.getConnection(url, user, password);
return con;
}
public static void closeSource(Connection con, PreparedStatement ps) {
//7.关闭资源
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeSource(Connection con, PreparedStatement ps, ResultSet rs) {
//7.关闭资源
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
PreparedStatement 实现查询操作
简介:在查询时,数据库会返回满足过滤条件的结果集,需要用 java 中的 ResultSet类的实例接收。
那么从数据库返回的数据应该用什么数据结构接受呢?在 java 中,万事万物都是对象,所以有了 ORM 的编程思想。
/*
ORM 的编程思想 (Object Relational Mapping)
一个数据表对应一个 Java 类
表中的一条记录对应着一个对象
表中的一个字段对应着 Java 类中的一个属性
*/
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
//其他方法省略
}
@Test
public void testSelect() {
Connection con = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//1.获取连接
con = JDBCUtils.createConnection();
//2.预编译 sql 语句,返回 PreparedStatement 实例
String sql = "select id, name, email, birth from customers where id = ?";
ps = con.prepareStatement(sql);
//3.填充占位符
ps.setInt(1, 1);
//4.执行操作,并返回结果集
resultSet = ps.executeQuery();
//5.处理结果集
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
java.sql.Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
JDBCUtils.closeSource(con, ps, resultSet);
}
}
改进一:通过反射技术实现对一张表的通用查询
public class CustomersForQuery {
@Test
public void test() {
String sql = "select id, name, email from customers where id = ?";
Customer customer = queryForCustomers(sql, 3);
System.out.println(customer);//Customer{id=3, name='林志玲', email='linzl@gmail.com', birth=null}
}
public Customer queryForCustomers(String sql, Object... objs) {
Connection con = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//1.获取连接
con = JDBCUtils.createConnection();
//2.预编译 sql 语句,获取 PreparedStatement 对象
ps = con.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < objs.length; i++) {
ps.setObject(i + 1, objs[i]);
}
//4.获取结果集
resultSet = ps.executeQuery();
//5.获取结果集元数据,以获取列信息
ResultSetMetaData metaData = resultSet.getMetaData();
//6.处理数据,创建对象
if (resultSet.next()) {
Customer cust = new Customer();
for (int i = 0; i < metaData.getColumnCount(); i++) {
//通过 getColumnLabel() 获取列的别名来替换 getColumnName()
//因为当遇到列名与类的属性名不一致时,需要通过给列起别名来解决
//说明:如果在 sql 中没有给类起别名,则获取的还是列名
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = Customer.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(cust, resultSet.getObject(i + 1));
}
return cust;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//7.关闭资源
JDBCUtils.closeSource(con, ps, resultSet);
}
return null;
}
}
改进二:通过泛型实现对任意表的通用查询
public class QueryTest {
@Test
public void test(){
String sql = "select id, name, email, birth from customers where id = ?";
Customer customer = queryAny(Customer.class , sql, 4);
System.out.println(customer);//Customer{id=4, name='汤唯', email='tangw@sina.com', birth=1986-06-13}
sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = queryAny(Order.class, sql, 2);
System.out.println(order);//Order{orderId=2, orderName='BB', orderDate=2000-02-01}
}
public <T> T queryAny(Class<T> clazz, String sql, Object... objs) {
Connection con = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//1.获取连接
con = JDBCUtils.createConnection();
//2.预编译 sql 语句,获取 PreparedStatement 对象
ps = con.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < objs.length; i++) {
ps.setObject(i + 1, objs[i]);
}
//4.获取结果集
resultSet = ps.executeQuery();
//5.获取结果集元数据,以获取列信息
ResultSetMetaData metaData = resultSet.getMetaData();
//6.处理数据,创建对象
if (resultSet.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
for (int i = 0; i < metaData.getColumnCount(); i++) {
//通过 getColumnLabel() 获取列的别名来替换 getColumnName()
//因为当遇到列名与类的属性名不一致时,需要通过给列起别名来解决
//说明:如果在 sql 中没有给类起别名,则获取的还是列名
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, resultSet.getObject(i + 1));
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//7.关闭资源
JDBCUtils.closeSource(con, ps, resultSet);
}
return null;
}
}
PreparedStatement 实现对 Blob 类型操作
public class BlobTest {
@Test
public void test() {
Connection con = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
InputStream is = null;
FileOutputStream fos = null;
try {
con = JDBCUtils.createConnection();
String sql = "select photo from customers where id = ?";
ps = con.prepareStatement(sql);
ps.setInt(1, 16);
resultSet = ps.executeQuery();
if (resultSet.next()) {
Blob blob = resultSet.getBlob("photo");
is = blob.getBinaryStream();
fos = new FileOutputStream("guaitang.jpg");
int cnt;
byte[] buff = new byte[1024];
while ((cnt = is.read(buff)) != -1) {
fos.write(buff, 0, cnt);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeSource(con, ps, resultSet);
try {
if (is != null)
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
@Test
public void test1() {
Connection con = null;
PreparedStatement ps = null;
FileInputStream fis = null;
try {
con = JDBCUtils.createConnection();
String sql = "insert into customers(name, email, birth, photo) values(?, ?, ?, ?)";
ps = con.prepareStatement(sql);
fis = new FileInputStream("tang.jpg");
ps.setString(1,"汤圆");
ps.setString(2,"tangyuan@126.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parse = simpleDateFormat.parse("2000-01-01");
ps.setDate(3,new java.sql.Date(parse.getTime()));
ps.setBlob(4, fis);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeSource(con, ps);
try {
if(fis != null)
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}