一、定义数据库和表
create database animal;
CREATE TABLE `pet` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`owner` varchar(20) DEFAULT NULL,
`species` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
二、连接数据库并定义数据库操作基本方法的几个工具类
（1）数据库连接及查询更新操作的封装
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* 模拟单例模式
* */
public class DBConn {
// 定义connection对象
private static Connection conn;
// 私有构造函数
private DBConn() {
}
// 返回连接对象
public static Connection getConn() {
if (conn == null) {
try {
long startTime = System.currentTimeMillis();
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/animal?user=root&generateSimpleParameterMetadata=true&password=root&useUnicode=true&characterEncoding=UTF-8");
long endTime = System.currentTimeMillis();
System.out.println("耗时的操作：" + (endTime - startTime));
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
// 更新的封装操作
public static boolean update(String sql, Object args[]) {
// 1.声明返回值变量
boolean flag = false;
// 2.获取预处理对象
PreparedStatement pstmt = null;
try {
pstmt = getConn().prepareStatement(sql);
// 3.为占位符赋值
int index = 1;
// 4.遍历赋值
for (Object arg : args) {
pstmt.setObject(index++, arg);
}
// 5.执行sql语句
int num = pstmt.executeUpdate();
if (num > 0) {
flag = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConn.release(null, pstmt);
}
return flag;
}
// 查找的封装操作
public static <T> List<T> query(String sql, Object args[],
IResultSetHandle<T> irsh) {
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = getConn().prepareStatement(sql);
if (args != null) {
int index = 1;
for (Object arg : args) {
pstmt.setObject(index++, arg);
}
}
rs = pstmt.executeQuery();
//交给别人处理
return irsh.handle(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConn.release(rs, pstmt);
}
return null;
}
// 释放资源
public static void release(ResultSet rs, PreparedStatement stmt) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
（2）定义IResultSetHandle接口供DBConn实现
import java.sql.ResultSet;
import java.util.List;
public interface IResultSetHandle<T> {
List<T> handle(ResultSet rs);
}
（3）日期转换
public class DateUtil {
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
public static String DateToStr(Date date) {
return sdf.format(date);
}
}
三、定义实体类
public class Pet implements Serializable{
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String owner;
private String species;
private String sex;
private Date birth;
private Date death;
public Pet() {
super();
// TODO Auto-generated constructor stub
}
public Pet(Integer id, String name, String owner, String species,
String sex, Date birth, Date death) {
super();
this.id = id;
this.name = name;
this.owner = owner;
this.species = species;
this.sex = sex;
this.birth = birth;
this.death = death;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getOwner() {
return owner;
}
public void setOwner(String owner) {
this.owner = owner;
}
public String getSpecies() {
return species;
}
public void setSpecies(String species) {
this.species = species;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Date getDeath() {
return death;
}
public void setDeath(Date death) {
this.death = death;
}
@Override
public String toString() {
return "Pet [id=" + id + ", name=" + name + ", owner=" + owner
+ ", species=" + species + ", sex=" + sex + ", birth=" + birth
+ ", death=" + death + "]";
}
}
四、对实体Bean操作的接口封装
public interface PetDao {
//插入实体
boolean insert(Pet entity);
//更新实体
boolean update(Pet entity);
//删除实体
boolean delete(Pet entity);
//根据id删除实体
boolean delete(Integer id);
//根据id、查询实体
Pet getObjectById(Integer id);
//查询所有实体
List<Pet> getObjects();
//根据用户查询实体
List<Pet> getObjectsByOwner(String owner);
}
五、对实体Bean操作接口的实现
public class PetDaoImpl implements PetDao {
@Override
public boolean insert(Pet entity) {
String sql = "insert into pet(name,owner,species,sex,birth,death)values(?,?,?,?,?,?)";
return DBConn.update(
sql,
new Object[] { entity.getName(), entity.getOwner(),
entity.getSpecies(), entity.getSex(),
entity.getBirth(), entity.getDeath() });
}
@Override
public boolean update(Pet entity) {
String sql = "update pet set name=?,owner=?,species=?,sex=?,birth=?,death=? where id=?";
return DBConn.update(sql,new Object[] { entity.getName(), entity.getOwner(),
entity.getSpecies(), entity.getSex(),
entity.getBirth(), entity.getDeath(), entity.getId() });
}
@Override
public boolean delete(Pet entity) {
return delete(entity.getId());
}
@Override
public boolean delete(Integer id) {
String sql = "delete from pet where id=?";
return DBConn.update(sql, new Object[] { id });
}
@Override
public Pet getObjectById(Integer id) {
String sql = "select id,name,owner,species,sex,birth,death from pet where id=?";
return (Pet) DBConn.query(sql, new Object[]{id}, new IResultSetHandle<Pet>() {
@SuppressWarnings("rawtypes")
public List<Pet> handle(ResultSet rs) {
List<Pet> entities=new ArrayList<Pet>();
Class cls=Pet.class;
try {
while(rs.next()){
Pet pet=(Pet) cls.newInstance();
//Field[] field=cls.getDeclaredFields();
for(int i=0;i<rs.getMetaData().getColumnCount();i++){
//Field f=field[i];
Field f=cls.getDeclaredField(rs.getMetaData().getColumnName(i+1));
//暴力操作
f.setAccessible(true);
f.set(pet, rs.getObject(i+1));
}
entities.add(pet);
}
} catch (Exception e) {
e.printStackTrace();
}
return entities;
}
}).get(0);
}
@Override
public List<Pet> getObjects() {
String sql = "select id,name,owner,species,sex,birth,death from pet ";
return DBConn.query(sql, null, new IResultSetHandle<Pet>() {
public List<Pet> handle(ResultSet rs) {
List<Pet> entities=new ArrayList<Pet>();
@SuppressWarnings("rawtypes")
Class cls=Pet.class;
try {
while(rs.next()){
Pet pet=(Pet) cls.newInstance();
//Field[] field=cls.getDeclaredFields();
for(int i=0;i<rs.getMetaData().getColumnCount();i++){
//Field f=field[i];
Field f=cls.getDeclaredField(rs.getMetaData().getColumnName(i+1));
f.setAccessible(true);
f.set(pet,rs.getObject(i+1));
}
entities.add(pet);
}
} catch (Exception e) {
e.printStackTrace();
}
return entities;
}
});
}
@Override
public List<Pet> getObjectsByOwner(String owner) {
String sql = "select id,name,owner,species,sex,birth,death from pet where owner=?";
return DBConn.query(sql, new Object[]{owner}, new IResultSetHandle<Pet>() {
public List<Pet> handle(ResultSet rs) {
List<Pet> entities=new ArrayList<Pet>();
@SuppressWarnings("rawtypes")
Class cls=Pet.class;
try {
while(rs.next()){
Pet pet=(Pet)cls.newInstance();
//Field[] field=cls.getDeclaredFields();
for(int i=0;i<rs.getMetaData().getColumnCount();i++){
//Field f=field[i];
Field f=cls.getDeclaredField(rs.getMetaData().getColumnName(i+1));
f.setAccessible(true);
f.set(pet, rs.getObject(i+1));
}
entities.add(pet);
}
} catch (Exception e) {
e.printStackTrace();
}
return entities;
}
});
}
}
六、防sql注入的测试
@Test
public void test() {
List<Pet> entities=petDao.getObjectsByOwner("' or '1'='1'");
for(Pet en:entities){
System.out.println(en.toString());
}
}
如果使用以下方法则会被注入
@Override
public Pet getObjectById(Integer id) {
Pet entity = null;
conn = DBConn.getConn();
String sql = "select id,name,owner,species,sex,birth,death from pet where id=?";
try {
pstmt = conn.prepareStatement(sql);
int index = 1;
pstmt.setObject(index++, id);
rs = pstmt.executeQuery();
if (rs.next()) {
entity = new Pet();
entity.setId(rs.getInt("id"));
entity.setName(rs.getString("name"));
entity.setOwner(rs.getString("owner"));
entity.setSpecies(rs.getString("species"));
entity.setSex(rs.getString("sex"));
entity.setBirth(rs.getDate("birth"));
entity.setDeath(rs.getDate("death"));
}
DBConn.release(rs, pstmt);
} catch (SQLException e) {
e.printStackTrace();
}
return entity;
}