这两天学习了一下jdbc的封装，依据的是下面这篇
http://wenku.baidu.com/link?url=FaFDmQouYkKO24ApATHYmA5QzUcj-UE-7RSSZaBWPqkKB8i13eYw2LGGEsgs_BRyBf7vB_zgB0vBxFXvhXhmLzBfBEPzPCvMvzMvesUwOzW
然后在它的基础上作了修改，简化参数，做了数据类型方面的休整。还不算完全成型，因为对“0”的处理还没有好的解决方案。下面贴出代码。
先贴一个Student实体类
package com.some.entity; import java.io.Serializable;
import java.sql.Date; public class Student implements Serializable {
private int id;
private String stuNumber;
private String stuPassword;
private String stuName;
private String stuSex;
private String stuIdentification;
private String stuIsGat;
private String stuPhone;
private int departmentId;
private int majorId;
private String gradeNow;
private int nationMark;
private String bithday;// 格式为2015/10/13
private String address;
private String politics; public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getStuNumber() {
return stuNumber;
} public void setStuNumber(String stuNumber) {
this.stuNumber = stuNumber;
} public String getStuPassword() {
return stuPassword;
} public void setStuPassword(String stuPassword) {
this.stuPassword = stuPassword;
} public String getStuName() {
return stuName;
} public void setStuName(String stuName) {
this.stuName = stuName;
} public String getStuSex() {
return stuSex;
} public void setStuSex(String stuSex) {
this.stuSex = stuSex;
} public String getStuIdentification() {
return stuIdentification;
} public void setStuIdentification(String stuIdentification) {
this.stuIdentification = stuIdentification;
} public String getStuIsGat() {
return stuIsGat;
} public void setStuIsGat(String stuIsGat) {
this.stuIsGat = stuIsGat;
} public String getStuPhone() {
return stuPhone;
} public void setStuPhone(String stuPhone) {
this.stuPhone = stuPhone;
} public int getDepartmentId() {
return departmentId;
} public void setDepartmentId(int departmentId) {
this.departmentId = departmentId;
} public int getMajorId() {
return majorId;
} public void setMajorId(int majorId) {
this.majorId = majorId;
} public String getGradeNow() {
return gradeNow;
} public void setGradeNow(String gradeNow) {
this.gradeNow = gradeNow;
} public int getNationMark() {
return nationMark;
} public void setNationMark(int nationMark) {
this.nationMark = nationMark;
} public String getBithday() {
return bithday;
} public void setBithday(String bithday) {
this.bithday = bithday;
} public String getAddress() {
return address;
} public void setAddress(String address) {
this.address = address;
} public String getPolitics() {
return politics;
} public void setPolitics(String politics) {
this.politics = politics;
}
}
Student.java
然后是数据库连接工具类
package com.sql.util; import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException; import com.mysql.jdbc.PreparedStatement; public class DatabaseConnection {
private static final String driver = "com.mysql.jdbc.Driver";// 数据库驱动
private static final String url = "jdbc:mysql://localhost:3306/sports_test";// 链接数据库名称
// ,localhost替换成域名
private static final String user = "root";// mysql的登陆用户名
private static final String password = "1234";// mysql的登陆密码
private Connection conn = null; public DatabaseConnection() throws Exception {// 在构造方法中进行数据库连接
try {
Class.forName(driver);//实现静态方法
this.conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
} public Connection getConnection() {
return this.conn;
} public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} public static void closePreparedStatement(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DatabaseConnection.java
顺便贴一个简易hash函数
package com.change.util; import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException; public final class GetHash {
public static String getMD5(String str){
String hashedStr=null;
try{
MessageDigest md=MessageDigest.getInstance("MD5");
md.update(str.getBytes());
byte ss[]=md.digest();
hashedStr=bytes2String(ss);
}catch(NoSuchAlgorithmException e){ }
return hashedStr;
} private static String bytes2String(byte[] aa){
String hash="";
for(int i=0;i<aa.length;i++){
int temp;
if(aa[i]<0){
temp=256+aa[i];
}else{
temp=aa[i];
}
if(temp<16){
hash+="0";
}
hash+=Integer.toString(temp,16);
}
hash=hash.toUpperCase();
return hash;
} // public static void main(String[] args) {
// String a="123456";
// String b="1234567";
// String c="fast";
// String hashA=GetHash.getMD5(a);
// String hashB=GetHash.getMD5(b);
// String hashC=GetHash.getMD5(c);
// System.out.println(hashA);
// System.out.println(hashB);
// System.out.println(hashC);
// //E10ADC3949BA59ABBE56E057F20F883E
// //FCEA920F7412B5DA7BE0CF42B8C93759
// //31D4541B8E926A24F0C9B835B68CFDF3
// }
}
GetHash.java
顺便贴一个转换编码的类，有时做下载功能的时候需要用到
package com.change.util;
import java.io.UnsupportedEncodingException;
public class ChangeISO {
public static String parseGBK(String sIn) {
if ((sIn == null) || (sIn.equals(""))) {
return sIn;
}
try {
return new String(sIn.getBytes("GBK"), "ISO-8859-1");
} catch (UnsupportedEncodingException usex) {
}
return sIn;
}
public static String parseUTF8(String sIn) {
if ((sIn == null) || (sIn.equals(""))) {
return sIn;
}
try {
return new String(sIn.getBytes("UTF-8"), "ISO-8859-1");
} catch (UnsupportedEncodingException usex) {
}
return sIn;
}
}
Change
最后就是jdbc增删查改的封装了，有了这个感觉方便很多，不需要像以前一个操作写一大坨函数，我真是太笨了= =
package com.sql.util; import java.security.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal; import com.change.util.GetHash;
import com.some.entity.Student; /**
* @see 这个类用于封装sql操作
*/
public class DatabaseOperator { /**
* @param conn
* Connection对象
* @param obj
* vo对象(实体)
* @param sql
* StringBuilder 对象
* @return List集合
* @throws Exception
*/ public static List<?> getSelectList(Connection conn, Object obj,
StringBuilder sql) {
int isFirstParameter = 0;
// 用于装入vo对象中设置属性值的真实属性值
List<Object> fieldValue = new ArrayList<>();
// 返回一个list集合，装的是执行过查询的集合，集合resultList装的一般是vo对象
List<Object> resultList = new ArrayList<>(); try {
// 获取对象的所属的类
Class<?> c = obj.getClass();
// 取得本类的全部属性
Field[] f = c.getDeclaredFields(); for (int i = 0; i < f.length; i++) {
String name = f[i].getName();
String get = DatabaseOperator.getGos("get", name); // Java 反射机制中 getMethod()和getDeclaredMethod()区别
// getMethods()和getDeclaredMethods()区别
// 前者只返回共有方法 后者返回全部3种（不包括继承）
Method m = c.getDeclaredMethod(get);
Object value = m.invoke(obj);
if (value != null && value != ""
&& !String.valueOf(value).equals("0")) {
System.out.println(value);
// 如果是第一个条件参数，不需要加上and
if (isFirstParameter == 0) {
sql.append(" " + name + " = ?");
isFirstParameter++;
// 填入要附加的条件参数
fieldValue.add(value);
} else {
sql.append(" and " + name + " = ?");
// 填入要附加的条件参数
fieldValue.add(value);
} }
}
System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql.toString());
for (int i = 0; i < fieldValue.size(); i++) {
// System.out.println(fieldValue.size());
// System.out.println(fieldValue.get(i));
ps.setObject(i + 1, fieldValue.get(i));
}
// 定义结果集
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Object o = c.newInstance();
// f是本类全部属性
for (int i = 0; i < f.length; i++) {
String name = f[i].getName();
// System.out.println(name);
String set = DatabaseOperator.getGos("set", name);
// System.out.println(set);
// 获取类的set方法
// System.out.println(f[i].getType());
Method m = c.getDeclaredMethod(set, f[i].getType());
m.setAccessible(true);
// System.out.println(m.getName());
Object value = rs.getObject(i + 1);
// System.out.println(value + "======");
if (value != null) {
// String r = f[i].getType().toString();
// System.out.println(r);
String rsType = value.getClass().getSimpleName();
// System.out.println(rsType + "++++"); // 判断从数据库读出的数据的数据类型，这里比较奇怪
if (rsType.equals("Long")) {
m.invoke(o, Integer.parseInt(value.toString()));
} else if (rsType.equals("Integer")) {
m.invoke(o, value);
} else if (rsType.equals("Double")) {
m.invoke(o, ((BigDecimal) value).doubleValue());
} else if (rsType.equals("Float")) {
m.invoke(o, ((BigDecimal) value).floatValue());
} else if (rsType.equals("Date")
|| rsType.equals("Timestamp")) {
// System.out.println("da");
if (rsType.equals("Date")) {
m.invoke(o, ((Date) value).toString());
} else {
m.invoke(o, ((Timestamp) value).toString());
}
} else if (rsType.equals("String")) {
m.invoke(o, value);
} else {
// System.out.println("未知");
}
}
}
resultList.add(o);
}
return resultList; } catch (Exception e) {
e.printStackTrace();
} return resultList;
} /**
*
* @param conn
* Connection对象
* @param sql1
* StringBuilder 可以增加长度，用于拼接指定的需要插入的列名
* @param obj
* vo对象
* @return int 0，1执行结果
* @throws Exception
*/
public static int doInsert(Connection conn, StringBuilder sql1, Object obj)
throws Exception {
Class<?> c = obj.getClass();
Field[] f = c.getDeclaredFields();
// StringBuilder 可以增加长度，用于拼接values后面的属性值
StringBuilder sql2 = new StringBuilder();
// 用于装入vo对象中设置属性值的真实属性值
List<Object> fieldValue = new ArrayList<>(); sql1.append("(");
sql2.append("values(");
for (int i = 0; i < f.length; i++) {
String name = f[i].getName();
String get = getGos("get", name);
Method m = c.getDeclaredMethod(get);
Object value = m.invoke(obj); if (value != null && value != ""
&& !String.valueOf(value).equals("0")) {
sql1.append(name + ",");
sql2.append("?,");
fieldValue.add(value);
}
} int j = sql1.length() - 1;
if (sql1.lastIndexOf(",") == j) {
int start = sql1.length() - 1;
int end = sql1.length();
sql1.replace(start, end, "");
}
int k = sql2.length() - 1;
if (sql2.lastIndexOf(",") == k) {
int start = sql2.length() - 1;
int end = sql2.length();
sql2.replace(start, end, "");
}
sql1.append(")");
sql2.append(")");
StringBuilder sql = sql1.append(sql2);
System.out.println(sql); PreparedStatement ps = conn.prepareStatement(sql.toString());
// System.out.println(fieldValue.size());
for (int i = 0; i < fieldValue.size(); i++) {
// System.out.println(fieldValue.get(i));
ps.setObject(i + 1, fieldValue.get(i));
}
// 执行
int result = ps.executeUpdate();
return result;
} /**
* @param conn
* Connection对象
* @param sql
* 删除的sql语句(index:delete from student)
* @param id
* 删除的主键
* @return int 0，1执行结果
*/
public static int doDelete(Connection conn, String sql, String primaryKey,
int id) {
sql = sql + " where " + primaryKey + " = ?";
int result = 0;
try {
System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
} /**
* @param conn
* Connection对象
* @param sql
* 要执行的sql语句
* @param obj
* 一般是vo对象
* @param primaryKey
* 表中的主键， 更新是基于主键更新的
* @return int 0，1执行结果
* @throws
*/
public static int doUpdate(Connection conn, StringBuilder sql, Object obj,
String primaryKey, Integer id) throws Exception {
Class<?> c = obj.getClass();
Field[] f = c.getDeclaredFields();
// list类型，用于在程序计数传入的属性值
List<Object> fieldValue = new ArrayList<>(); sql.append(" set"); for (int i = 0; i < f.length; i++) {
String name = f[i].getName();
String get = getGos("get", name);
Method m = c.getDeclaredMethod(get);
Object value = m.invoke(obj); int j = name.toLowerCase().indexOf(primaryKey.toLowerCase());
if (value != null && value != "" && j < 0) {
// System.out.println(f[i].getName());
sql.append(" " + name + " = ?,");
fieldValue.add(value);
}
} // 去掉最后一个逗号
int k = sql.length() - 1;
if (sql.lastIndexOf(",") == k) {
int start = sql.length() - 1;
int end = sql.length();
sql.replace(start, end, "");
} if (id != null) {
sql.append(" where " + primaryKey + " = ?");
} System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql.toString());
for (int i = 0; i < fieldValue.size(); i++) {
// System.out.println(fieldValue.get(i));
ps.setObject(i + 1, fieldValue.get(i));
}
if (id != null) {
ps.setObject(fieldValue.size() + 1, id);
System.out.println(id);
}
int result = ps.executeUpdate();
System.out.println(result);
return result;
} /**
* @see getGos是获取get或者set，用于得到一个属性的get和set方法，例如getName()
* @param s:set 或者 get @param name:属性
*
* @return String类型
*/
public static String getGos(String s, String name) {
String str = s + name.substring(0, 1).toUpperCase() + name.substring(1);
return str;
} public static void main(String[] args) {
DatabaseConnection dc = null;
try {
dc = new DatabaseConnection();
Connection conn = dc.getConnection(); // // 查询
// Student student1 = new Student();
// // student1.setStuNumber("14020031096");
// StringBuilder sql1 = new StringBuilder();
// // sql1.append("select * from student where");
// sql1.append("select * from student");
// @SuppressWarnings("unchecked")
// List<Student> stuList = (List<Student>) DatabaseOperator
// .getSelectList(conn, student1, sql1);
// System.out.println(stuList.get(0).getStuNumber());
// System.out.println(stuList.get(0).getId());
// for (int i = 0; i < stuList.size(); i++) {
// System.out.println(stuList.get(i).getStuName());
// } // 增加
// Student student2 = new Student();
// student2.setStuName("萌重宝宝");
// student2.setStuNumber("22222222222");
// student2.setStuPassword(GetHash.getMD5("123456"));
// StringBuilder sql2 = new StringBuilder();
// sql2.append("insert into student");
// int result=doInsert(conn, sql2, student2);
// if(result==1){
// System.out.println("insert成功");
// }else{
// System.out.println("insert失败");
// } // 删除
// String sql3 = "delete from student";
// String primaryKey = "id";
// int id = 15458;
// int result=doDelete(conn, sql3, primaryKey, id);
// if(result==1){
// System.out.println("delete成功");
// }else{
// System.out.println("delete失败");
// } // 更新
// Student student4 = new Student();
// student4.setStuName("萌重儿子");
// StringBuilder sql4 = new StringBuilder();
// sql4.append("update student");
// String primaryKey = "id";
// int id = 15456;
// DatabaseOperator.doUpdate(conn, sql4, student4, primaryKey, id);
} catch (Exception e) {
e.printStackTrace();
} }
}
DatabaseOperator
主要是4个函数，还有一个拼接字符串函数，主函数是使用范例。
啦啦啦跑步去