mysql封装增删改查_jdbc封装一行代码实现增删改查

一行代码实现增删改查、支持自定义结果集。

2个测试类(先看效果)

public class UserDao extends Dao {

public User login(User user) throws Exception{

return (User) executeQueryOne("select * from user where phone=? and password=md5(?)",

User.class,user.getPhone(),user.getPassword() );

}

}

public class CommentDao extends Dao {

public List getAll() throws InvocationTargetException, Exception{

return executeQueryList("select * from comment", Comment.class);

}

public boolean updateClickNum(int id) throws SQLException{

return executeUpdate("update comment set clicknum = clicknum + 1 where id = ?;", id) >0;

}

}

三个核心类

com.utils.Dao.java

com.utils.MyResultHandler.java

com.utils.MyResultHandlerAdapter.java

分别如下：

package com.utils;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

* 将jdbc的共同操作提取出来进行封装。

*

* @author Administrator

*

*/

public class Dao {

protected Connection conn;

protected Statement stat;

protected PreparedStatement ps;

protected ResultSet rs;

protected int affectedRows;//

static {

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public Connection getConn() throws SQLException {

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/chat", "root", "123456");

return conn;

}

public List executeQueryList(String sql, Class cls, Object... params)

throws Exception, InvocationTargetException {

conn = getConn();

ps = conn.prepareStatement(sql);

setParams(rs, params);

rs = ps.executeQuery();

List list = new ArrayList();

while (rs.next()) {

Object data = invoteSetter(cls);

list.add(data);

}

close();

return list;

}

public Object executeQueryOne(String sql, Class cls, Object... params) throws Exception{

conn = getConn();

ps = conn.prepareStatement(sql);

setParams(rs, params);

rs = ps.executeQuery();

Object data = null;

if (rs.next()) {

data = invoteSetter(cls);

}

close();

return data;

}

//结果集自定义封装。

public void executeQueryOne(String sql,MyResultHandler hand, Object... params)

throws Exception {

conn = getConn();

ps = conn.prepareStatement(sql);

setParams(rs, params);

rs = ps.executeQuery();

if (rs.next()) {

hand.invokeOneTime(rs);

}

close();

}

//结果集自定义封装。

public void executeQueryList(String sql,MyResultHandler hand, Object... params)

throws Exception {

conn = getConn();

ps = conn.prepareStatement(sql);

setParams(rs, params);

rs = ps.executeQuery();

while (rs.next()) {

hand.invokeEveryTime(rs);

}

close();

}

private Object invoteSetter(Class cls) throws InstantiationException, IllegalAccessException, NoSuchMethodException, SecurityException, SQLException, IllegalArgumentException, InvocationTargetException {

Object obj = cls.newInstance();// 反射创建对象。

// ① 当前cls所在的类，定义了哪些属性。才能知道setter方法的名字。

// ② 每个属性是什么类型的。才能知道要调用rs.getInt还是rs.getString ...

Field[] fs = cls.getDeclaredFields();// 获取cls类的所有定义的属性。

for (Field f : fs) {

String fieldname = f.getName();// 属性的名字。

Class> type = f.getType();// 属性的类型的class对象。 int -- int.class

// String --String.class

String setter = "set" + fieldname.substring(0, 1).toUpperCase()

+ fieldname.substring(1, fieldname.length());

Method settermethod = cls.getMethod(setter, type);

if (settermethod == null)

continue;// 如果方法不存在则跳过。

Object value = null;

if (type == int.class || type == Integer.class) {

value = rs.getInt(fieldname);

} else if (type == String.class) {

value = rs.getString(fieldname);

} else if (type == Date.class) {

value = rs.getDate(fieldname);

}//此处还应该有更多的判断。

// 反射调用方法。setXXX方法。进行赋值。

settermethod.invoke(obj, value);

}

return obj;

}

public int executeUpdate(String sql, Object... params) throws SQLException {

conn = getConn();

ps = conn.prepareStatement(sql);

setParams(rs, params);

affectedRows = ps.executeUpdate();

close();

return affectedRows;

}

private void setParams(ResultSet rs, Object... params) throws SQLException {

for (int i = 0; i < params.length; i++) {

Object v = params[i];

if(v == null)continue;

if (v instanceof Integer) {

ps.setInt(i + 1, (int) v);

} else if (v instanceof String) {

ps.setString(i + 1, (String) v);

} else if (v instanceof java.util.Date) {

}

}

}

public void close() {

close(conn, stat, rs);

}

public static void close(Connection conn, Statement stat, ResultSet rs) {

if (conn != null)

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (stat != null)

try {

stat.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (rs != null)

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

package com.utils;

import java.sql.ResultSet;

public interface MyResultHandler {

void invokeOneTime(ResultSet rs);

Object getData();

void invokeEveryTime(ResultSet rs);

}

package com.utils;

import java.sql.ResultSet;

public abstract class MyResultHandlerAdapter implements MyResultHandler {

protected Object data = null;

@Override

public void invokeOneTime(ResultSet rs) { }

@Override

public Object getData() { return data;

}

@Override

public void invokeEveryTime(ResultSet rs) { }

}