一、定义数据库和表
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; }