mysql之数据库连接的方法封装及防sql注入

一、定义数据库和表

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 [;

}

}

四、对实体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 ;

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 ;

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 ;

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 ;

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;

}