1、提供customer类
package jdbc_3_bean;
import java.util.Date;
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 +
'}';
}
}
2、创建BaseDAO抽象类
package jdbc_6_dao;
import jdbc_2_util.JDBCUtils;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public abstract class BaseDao {
//针对于不同表的一条数据查询
public static <T> T getInstance(Connection conn,Class<T> clazz ,String sql, Object ...args){
PreparedStatement ps=null;
ResultSet rs=null;
try{
//获得实例
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 value= rs.getObject(i+1);
//获取每列的别名
String columnLabel=rsmd.getColumnLabel(i+1);
//通过反射将列名赋值给对象的属性
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
//针对不同表的多条数据查询
public static <T>List<T> getForList(Connection conn, Class<T> clazz,String sql, Object ...args){
PreparedStatement ps=null;
ResultSet rs=null;
try{
//获得实例
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();
//创建集合对象
ArrayList<T> list=new ArrayList<T>();
while (rs.next()){
T t=clazz.newInstance();
for(int i=0;i<columnCount;i++){
//获取每列的值
Object value= rs.getObject(i+1);
//获取每列的别名
String columnLabel=rsmd.getColumnLabel(i+1);
//通过反射将列名赋值给对象的属性
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
list.add(t);
}
return list;
}catch (Exception e){
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
//更新
public static void update(Connection conn,String sql, Object... args) {
PreparedStatement ps = null;
try {
//2、预编译SQL语句,返回PreparedStatement的实例
//#########由参数传入########
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、关闭资源
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//返回列值
public static <E> E getValue(Connection conn,String sql,Object...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try{
ps=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs=ps.executeQuery();
if(rs.next()){
return (E)rs.getObject(1);
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
3、创建接口,定义规范
package jdbc_6_dao;
import jdbc_3_bean.Customer;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
public interface CustomerDAO {
//将customer对象添加到数据库中
void insert(Connection conn, Customer customer);
//针对指定的id,删除表中的一条记录
void deleteById(Connection conn,int id);
//针对内存中的cust对象,去修改表中指定的记录
void update(Connection conn,Customer cust);
//针对指定的id查询得到对应的customer对象
Customer getCustomerById (Connection conn,int id);
//查询表中所有记录构成的集合
List<Customer> getAll(Connection conn);
//查询列数
Long getCount(Connection conn);
//返回最大生日
Date getMaxBirth(Connection conn);
}
4、继承抽象类实现接口
package jdbc_6_dao;
import jdbc_2_util.JDBCUtils;
import jdbc_3_bean.Customer;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
public class CustomerDAOImpl extends BaseDao implements CustomerDAO {
public static void main(String[] args) {
Connection connection=null;
try{
CustomerDAOImpl cust=new CustomerDAOImpl();
Customer customer=new Customer(1,"沈子畅","haob@126.com",new Date(17572725525L));
connection= JDBCUtils.getConnection();
cust.insert(connection,customer);
System.out.println("添加成功");
}catch (Exception e){
e.printStackTrace();
}finally {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
@Override
public void insert(Connection conn, Customer customer) {
String sql="insert into customers(name,email,birth)value(?,?,?)";
update(conn,sql,customer.getName(),customer.getEmail(),customer.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from customers where id=?";
update(conn,sql,id);