Prestatement的增、删、改、查
前言:
Statement的弊端:需要拼串sql语句,并存在sql注入问题
Prestatement相较于Statement的优点在于可防止SQL注入。
源码:
链接:https://pan.baidu.com/s/14Y9mPnCPDX013mCeJK7ydA
提取码:sdfo
复制这段内容后打开百度网盘手机App,操作更方便哦
目录结构
1.配置
1.1连接数据库的配置文件
jdbc.properties
user=root
password=shan5211314..
DB_URL=jdbc:mysql://localhost:3306/test
JDBC_DRIVER=com.mysql.jdbc.Driver
1.2 操作数据库的工具类
utils.JDBCUtils
package com.shan.util;
import com.shan.connection.ConnectionTest;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author shan
* @date 2021/5/7
* 操作数据库的工具类
*/
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 DB_URL = pros.getProperty("DB_URL");
String JDBC_DRIVER = pros.getProperty("JDBC_DRIVER");
//2.加载驱动
Class.forName(JDBC_DRIVER);
//3.获取连接
Connection conn = DriverManager.getConnection(DB_URL, user, password);
return conn;
}
//关闭数据库的连接操作
public static void closeResource(Connection conn, PreparedStatement ps){
//资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e){
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e){
e.printStackTrace();
}
}
// 查找的 资源关闭
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result){
//资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e){
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e){
e.printStackTrace();
}
try {
if(result != null)
result.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
1.3数据库表
1.4 java 和 sql对应的数据类型转换表
2.通用的增删改操作
PrepareStatementUpdateTest.java
package com.shan.preparestatement;
import com.shan.connection.ConnectionTest;
import com.shan.util.JDBCUtils;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;
/*
* 使用PrepareStatement,实现对数据的的增删改查操作
* 增删改;查
* */
public class PrepareStatementUpdateTest {
@Test
public void testCommentUpdate(){
// 删除
String sql = "delete from customers where id = ?";
update(sql,3);
// 更改
String sql1 = "update customers set name = ? where id = ?";
update(sql1, "莫扎特", 1);
// 添加
String sql2 = "insert into customers(name,email)values(?,?)"; //?,?表示占位符
update(sql2, "张三丰","zsf@126.com");
}
//通用的增删改操作
public void update(String sql, Object ...args){//sql中占位符的个数应该与可变形参的长度一致
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译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 {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
//修改customers表的一条记录
@Test
public void testUpdate() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句,返回PrepareStatement的实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1, "莫扎特");
ps.setObject(2, 2);
//4.执行
ps.execute();
} catch (Exception e){
e.printStackTrace();
}finally {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
//向customers表中添加一条记录
@Test
public void testInsert() {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.读取jdbc配置文件的基本信息
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String DB_URL = pros.getProperty("DB_URL");
String JDBC_DRIVER = pros.getProperty("JDBC_DRIVER");
//2.加载驱动
Class.forName(JDBC_DRIVER);
//3.获取连接
conn = DriverManager.getConnection(DB_URL, user, password);
System.out.println(conn);
//4.预编译sql语句,返回PreparedStatement实例
String sql = "insert into customers(name,email)values(?,?)"; //?,?表示占位符
ps = conn.prepareStatement(sql);
//5.填充占位符
ps.setString(1, "娜扎");
ps.setString(2, "nz@162.com");
// SimpleDateFormat data = new SimpleDateFormat("yyy-dmm-dd");
// java.util.Date date = data.parse("2021-5-7");
// ps.setDate(3, new Date(date.getTime()));
//6.执行操作
ps.execute();
} catch (Exception e){
e.printStackTrace();
}finally {
//7.资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e){
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
3.通用的查询操作
Customer.java
package com.shan.bean;
/**
* @author shan
* @date 2021/5/7
* ORM编程思想
* 一个数据表对应一个java类
* 表中的一条记录对应java类的一个对象
* 表中的一个字段对应java类的一个属性
* 需要知道 java 和 sql对应的数据类型转换表
* idea生成有参构造器和无参构造器 alt+insert ,选择constructor,选择select none ,就是无参;选择ok就是有参
*/
public class Customer {
private long id;
private String name;
private String email;
public Customer() {
super();
}
public Customer(long id, String name, String email) {
super();
this.id = id;
this.name = name;
this.email = email;
}
public long getId() {
return id;
}
public void setId(long 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;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
'}';
}
}
CustomerForQuery.java
package com.shan.preparestatement;
import com.shan.bean.Customer;
import com.shan.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/**
* @author shan
* @date 2021/5/7
* 针对于Customers表的查询操作
*/
public class CustomersForQuery {
@Test
public void testqueryForCustomers(){
String sql = "select id,name,email from customers where id = ?";
Customer customer = queryForCustomers(sql, 1);
System.out.println(customer);
}
// 针对customers表通用的操作
public Customer 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();
//通过ResultSetMetaData获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Customer cust = new Customer();
//处理结果集一行数据中的每一列
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
//给cust对象指定columnName的属性,赋值为columnvalue,通过反射
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(cust, columnValue);
}
return cust;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet result = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,phone from customers where id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1,1);
//执行并返回结果集
result = ps.executeQuery();
//处理结果集
if (result.next()) {//判断结果集的下一条是否有数据,如果有数据返回true,并指针下移
//获取当前数据的各个字段值
int id = result.getInt(1);
String name = result.getString(2);
String email = result.getString(3);
//方式一:
System.out.println("id = " + id + ",name = " + name + ",email = " + email);
//方式二:
Object[] data = new Object[]{id, name, email};
//方式三:将数据封装为一个对象(推荐)
Customer customer = new Customer(id, name, email);
System.out.println(customer);
}
}catch (Exception e){
e.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResource(conn, ps, result);
}
}
}
-
email);
//方式二: Object[] data = new Object[]{id, name, email}; //方式三:将数据封装为一个对象(推荐) Customer customer = new Customer(id, name, email); System.out.println(customer); } }catch (Exception e){ e.printStackTrace(); }finally { //关闭资源 JDBCUtils.closeResource(conn, ps, result); }
}
}