package java3.crud;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import org.junit.Test;
import java3.util.JDBCUtils;
public class PreparedStatementUpdateTest {
@Test
public void testCommonUpdate(){
// String sql="delete from customers where id=?";
// update(sql,3);
//order这个表名和关键字冲突，要用``包起来
String sql="update `order` set order_name=? where order_id=?";
update(sql,"qq",2);
}
//通用的增删改操作
//sql中占位符的个数与可变形参的长度相同
public void update(String sql,Object ...args) {
Connection conn=null;
PreparedStatement ps=null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnedtion();
//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);
}
}
//修改customer表的一条记录
@Test
public void testUpdate() {
Connection conn=null;
PreparedStatement ps=null;
try {
//1.数据库的连接
conn = JDBCUtils.getConnedtion();
//2.预编译sql语句，返回PreparedStatement的实例
String sql="update customers set name=? where id=?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1, "莫扎特");
ps.setObject(2, 18);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally{
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
//向Customer表中添加一条记录
@Test
public void testInsert() {
Connection conn=null;
PreparedStatement ps=null;
try {
//1.读取配置文件中的4个基本信息
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");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
conn = DriverManager.getConnection(url,user,password);
//4.预编译sql语句，返回PreparedStatement的实例
//?:占位符
String sql="insert into customers(name,email,birth)values(?,?,?)";
ps = conn.prepareStatement(sql);
//5.填充占位符
ps.setString(1, "孙悟空");
ps.setString(2, "abd@huaguoshan.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse("1000-01-01");
ps.setDate(3, new java.sql.Date(date.getTime()));
//6.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally{
//7.资源的关闭
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
package java3.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.junit.Test;
import java3.bean.Customer;
import java3.util.JDBCUtils;
/*
* 针对于Customer表的查询操作
*/
public class CustomerForQuery {
@Test
public void testQueryForCustomers() throws Exception{
String sql="select id,name,birth,email from customers where id=?";
Customer customer = queryForCustomers(sql, 13);
System.out.println(customer);
sql="select name,email from customers where name=?";
Customer queryForCustomers = queryForCustomers(sql, "周杰伦");
System.out.println(queryForCustomers);
}
/*
* 针对Customers表的通用查询操作
*/
public Customer queryForCustomers(String sql,Object...args) throws Exception{
Connection conn = JDBCUtils.getConnedtion();
PreparedStatement ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
ResultSet rs = ps.executeQuery();
//获取结果集的元数据：ResultSetMetaData
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);
//获取每个列的列名
//getColumnName():列名
//getColumnLabel():列的别名（如果没有别名，那么就返回列名）
String columnLabel = rsmd.getColumnLabel(i+1);
//给cusst对象指定的columnName属性，赋值为columnValue:通过反射
Field field = Customer.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(cust, columnValue);
}
return cust;
}
JDBCUtils.closeResource(conn, ps, rs);
return null;
}
@Test
public void testQuery1(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet resultSet=null;
try {
conn = JDBCUtils.getConnedtion();
String sql="select id,name,email,birth from customers where id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
//执行，并返回结果集
resultSet = ps.executeQuery();
//next():判断结果集的下一条是否有数据，如果有数据返回true，并指针下移；如果返回false，指针不会下移
if(resultSet.next()){
//获取当前这条数据的各个字段值
int id=resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(conn, ps,resultSet);
}
}
}
package java3.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;
import org.junit.Test;
import java3.bean.Order;
import java3.util.JDBCUtils;
/*
* 针对于order表的通用的查询操作
*/
public class OrderForQuery {
/*
* 针对于表的字段与类的属性名不相同的情况下
* 1.必须声明sql时，使用类的属性名来命名字段的别名
* 2.使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()来获取列的别名
* 说明：如果sql中没有给字段其别名，getColumnLabel()获取的就是列名
*/
@Test
public void testOrderForQuery() {
String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
Order orderForQuery = orderForQuery(sql, 1);
System.out.println(orderForQuery);
}
/*
* 通用的针对于order表的查询操作
*/
public Order orderForQuery(String sql,Object ...args) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnedtion();
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()){
Order order = new Order();
for(int i=0;i<columnCount;i++){
//列值
Object columnValue = rs.getObject(i+1);
//列的别名
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(conn, ps,rs);
}
return null;
}
@Test
public void testQuery1() throws Exception{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnedtion();
String sql="select order_id,order_name,order_date from `order` where order_id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
rs = ps.executeQuery();
if(rs.next()){
int id = (int) rs.getObject(1);
String name = (String) rs.getObject(2);
Date date = (Date) rs.getObject(3);
Order order = new Order(id, name, date);
System.out.println(order);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(conn, ps,rs);
}
}
}
package java3.crud;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import java3.bean.Customer;
import java3.bean.Order;
import java3.util.JDBCUtils;
/*
使用PreparedStatement实现针对于不同表的通用的查询操作
*/
public class PrepareStatementQueryTest {
@Test
public void testGetForList(){
String sql="select id,name,email from customers where id < ?";
List<Customer> forList = getForList(Customer.class, sql, 12);
// System.out.println(forList);
forList.forEach(System.out::println);
System.out.println();
String sql1="select order_id orderId,order_name orderName from `order`";
List<Order> forList2 = getForList(Order.class, sql1);
forList2.forEach(System.out::println);
}
public <T> List<T> getForList(Class<T> clazz,String sql,Object ...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnedtion();
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();
System.out.println(columnCount);
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while(rs.next()){
T t=clazz.newInstance();
for(int i=0;i<columnCount;i++){
Object columnValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testGetInstance(){
String sql="select id,name,email,birth from customers where id=?";
Customer instance = getInstance(Customer.class, sql, 12);
System.out.println(instance);
String sql1="select order_id orderId,order_name orderName from `order` where order_id=?";
Order instance2 = getInstance(Order.class, sql1, 1);
System.out.println(instance2);
}
/*
* 针对于不同表的查询操作，返回表中的一条记录
*/
public <T> T getInstance(Class<T> clazz,String sql,Object ...args) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnedtion();
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()){
T t=clazz.newInstance();
//处理结果集一行数据中的每一个列
for(int i=0;i<columnCount;i++){
//获取列值
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
String columnLabel = rsmd.getColumnLabel(i+1);
//给t对象指定的columnName属性，赋值为columnValue，通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
package java3.bean;
import java.util.Date;
/*
* ORM编程思想(object relational mapping)
* 一个数据表对应一个java类
* 表中的一条记录对应java类的一个对象
* 表中的一个字段对应java类的一个属性
*/
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
super();
}
public Customer(int id, String name, String email, Date birth) {
super();
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 + "]";
}
}
package java3.bean;
import java.util.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
super();
}
public Order(int orderId, String orderName, Date orderDate) {
super();
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 + "]";
}
}
package java3.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
* 操作数据库的工具类
*/
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnedtion() throws Exception{
//1.读取配置文件中的4个基本信息
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");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//关闭连接和statement的操纵
public static void closeResource(Connection conn,Statement ps){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭带结构集的资源的操作
public static void closeResource(Connection conn,Statement ps,ResultSet rs){
if(ps!=null){
try {
ps.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();
}
}
}
}
