目录
使用PreparedStatement调用SQL语句,实现对数据库的增删改查
1.最基本的更新数据库的一条操作:(jdbc.properties为配置文件,包含4个参数,移植性好)
2.将数据库连接,资源关闭,SQL语句(增删改查)封装为一个静态类
使用PreparedStatement调用SQL语句,实现对数据库的增删改查
1.最基本的更新数据库的一条操作:(jdbc.properties为配置文件,包含4个参数,移植性好)
package preparedstatement.curd;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Properties;
import org.junit.Test;
public class PreparedStatementUpdateTest {
@Test
public void test() throws Exception {
//1.获取连接的3个基本信息
InputStream is = ClassLoader.getSystemClassLoader()
.getResourceAsStream("jdbc.properties");//换行,方便浏览
Properties pros = new Properties();
pros.load(is);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
// System.out.println(conn);
//4.预编译SQL语句,返回PreparedStatement实例
String sql = "insert into customers (name,email,birth) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
//5.填充占位符,数据库下标从1开始
ps.setString(1, "周星驰");
ps.setString(2, "zhouxichi@qq.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//转换为SQL下的date
java.util.Date date = sdf.parse("2022-1-9");
ps.setDate(3, new java.sql.Date(date.getTime()));
//6.执行操作
ps.execute();
//7.资源关闭,最好使用try-catch进行操作,这里简写了
ps.close();
conn.close();
}
}
2.将数据库连接,资源关闭,SQL语句(增删改查)封装为一个静态类
package myjdbc.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
package myjdbc.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
//获取连接
public static Connection getConnection() throws Exception {
//1.获取连接的3个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//资源关闭
public static void closeResource(Connection conn,Statement statement) {
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//资源关闭,重载方法
public static void closeResource(Connection conn,Statement statement,ResultSet rs) {
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void update(String sql,Object...orgs) throws Exception {
//1.获取数据库连接
Connection conn = getConnection();
//2.预编译SQl语句,获取PreparedStatement实例
PreparedStatement ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0;i<orgs.length;i++) {
ps.setObject(i+1, orgs[i]);
}
//4.执行结果
ps.execute();
//5.关闭资源
closeResource(conn, ps);
}
}
3下面是具体的实例(使用上述自定义函数)
//执行操作
public class PreparedStatementUpdateTest02 {
//增删改通用操作
@Test
public void testUpdate() {
String sql = "update customers set email=? where id=?";
try {
myjdbc.utils.JDBCUtil.update(sql, "zxc@qq.com",19);
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
4.查询操作,同样使用了上述自定义函数
package preparedstatement.curd;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import myjdbc.utils.JDBCUtil;
public class PreparedStatementUpdateTest03 {
@Test
public void testQuery() throws Exception {
//1.获取连接
Connection conn = JDBCUtil.getConnection();
//2.获取PreparedStatement实例
String sql = "select id,name,email,birth from customers where name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, "周星驰");
//3.处理结果集
ResultSet rs = ps.executeQuery();
if(rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
//Customers是一个包含一下参数的一个javabean文件
Customers customs = new Customers(id, name, email, birth);
System.out.println(customs.toString());
}
//4.关闭资源
JDBCUtil.closeResource(conn, ps, rs);
}
}
具体操作:
5.查询操作进阶:自定义类去实现查询
package preparedstatement.curd;
/*
* 对PreparedStatementQuery的进阶
*
*/
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Random;
import org.junit.Test;
import myjdbc.utils.JDBCUtil;
public class PreparedStatementQuery02 {
@Test
public void test() throws Exception {
// String sql = "select * from customers where name = ?";
// String sql = "select id,name,email,birth from customers where name=?";
String sql = "select id,name,email,birth from customers where name=?";
Customers customers = getCustomers(sql, "周杰伦");
System.out.println(customers);
}
/*
* 新建一个方法,实现只要通过输入SQL语句和填充相应的占位符,即可返回查询对应表的对象
*/
public Customers getCustomers(String sql,Object...args) throws Exception {
//1.获取连接对象
Connection conn = JDBCUtil.getConnection();
//2.获取PreparedStatement实例
PreparedStatement ps = conn.prepareStatement(sql);
//填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1, args[i]);
}
//3.获取结果集
ResultSet rs = ps.executeQuery();
//4.获取元数据及列数
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
//5.把数据库中的每个属性都分别赋值给Customers对象中去(此对象也可为别的,看自己怎么定义)
if(rs.next()) {
Customers customers = new Customers();
//遍历使用列
for(int i=0;i<count;i++) {
//得到每列的值
Object columnValue = rs.getObject(i+1);
//得到每列的名称
String columnName = rsmd.getColumnLabel(i+1); //获取列的别名,无别名则为列名
// String columnName = rsmd.getColumnName(i+1); //此方法基本不再使用
//使用反射根据每个列名去设置对应的列值
Field field = Customers.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(customers, columnValue);
}
//关闭资源
JDBCUtil.closeResource(conn, ps, rs);
return customers;
}
//关闭资源
JDBCUtil.closeResource(conn, ps, rs);
return null;
}
}