目录
这个数据库小工具是在校的时间写的，把四个类复制到自己的项目内，就可以省去封装数据实体、解析数据实体的麻烦，开发效率很高，...
双手奉上，小工具的源码
1 数据库连接类
package web.dao.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DbConnection {
//这里用的是Sql Server 2008数据库
static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=数据库名";
static String username = "sa";
static String password = "888999";
static String driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName(driverClassName);
connection = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}2 类构建工具：主要用于通过指定类名和属性值构建这个实例化的对象。
package web.util;
import java.lang.reflect.Field;
import java.util.Map;
public class BuildObjectUtil {
/**用来把指定的Map内数据 赋值到 指定的类 的实例化对象的属性上，并返回这个实例化对象*/
@SuppressWarnings("unchecked")
public static <T> T build(Class<?> clazz,Map<String,Object> map) {
T T_Object=null;
try {
Object object=clazz.newInstance();
Field[] fields=clazz.getDeclaredFields();
for(Field field:fields) {
field.setAccessible(true);
String fieldName=field.getName();
Object value=map.get(fieldName);
if(value==null) {
continue;
}
field.set(object, value);
}
T_Object=(T) object;
} catch (Exception e) {
e.printStackTrace();
}
return T_Object;
}
/**返回类内的所有属性名*/
public static String[] getAttrNames(Class<?> clazz) {
Field[] fields=clazz.getDeclaredFields();
String[] attrs=new String[fields.length];
try {
for(int i=0;i<fields.length;i++) {
fields[i].setAccessible(true);
attrs[i]=fields[i].getName();
}
} catch (Exception e) {
e.printStackTrace();
}
return attrs;
}
}3 实体类解析工具：主要用于解析实体对象下的属性。
package web.util;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ResolveObjectUtil {
/**将List内的对象的属性，以List<Map>形式解析出来*/
public static <E> List<Map<String, Object>> resolveObjects(List<E> objects){
List<Map<String, Object>> elements=new ArrayList<Map<String,Object>>();
for(E obj:objects) {
elements.add(resolveObject(obj));
}
return elements;
}
/**解析对象内的属性，以Map形式解析出来*/
public static <E> Map<String,Object> resolveObject(E e) {
Map<String,Object> maps=new HashMap<String,Object>();
Class<? extends Object> clazz=e.getClass();
Field[] fields=clazz.getDeclaredFields();
for(Field field:fields) {
try {
field.setAccessible(true);
Object value=field.get(e);
maps.put(field.getName(), value);
} catch (Exception exc) {
exc.printStackTrace();
}
}
return maps;
}
}4 数据库工具类，所有的Dao接口实现直接调用这个就可以
package web.dao.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import web.util.BuildObjectUtil;
import web.util.ResolveObjectUtil;
public class SqlHelper {
private static final String BaseEntityPacage="web.entity";
private static SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
public static ResultSet executeQuery(String sql) {
ResultSet rs=null;
Connection conn=DbConnection.getConnection();
try {
PreparedStatement preparedStatement=conn.prepareStatement(sql);
rs=preparedStatement.executeQuery();
} catch (SQLException e) {
System.err.println("执行Sql出现错误：sql="+sql+" &|&msg="+e.getMessage());
}
return rs;
}
public static int executeNoQuery(String sql) {
int i=0;
Connection conn=DbConnection.getConnection();
try {
PreparedStatement preparedStatement=conn.prepareStatement(sql);
i=preparedStatement.executeUpdate();
} catch (SQLException e) {
System.err.println("执行Sql出现错误：sql="+sql+" &|&msg="+e.getMessage());
}
return i;
}
public static <T> T executeQueryOne(Class<?> clazz,String sql) {
List<T> list=executeQuery(clazz,sql);
if(list!=null&&list.size()>0) {
return list.get(0);
}
return null;
}
public static <T> List<T> executeQuery(Class<?> clazz,String sql) {
ArrayList<T> objects=new ArrayList<T>();
Connection conn=DbConnection.getConnection();
try {
PreparedStatement preparedStatement=conn.prepareStatement(sql);
ResultSet rs=preparedStatement.executeQuery();
Map<String,Object> map=new HashMap<String,Object>();
List<String> columns=getColumnsFromSql(sql);
if(columns==null) {
columns=new ArrayList<String>();
}
for(String attr:BuildObjectUtil.getAttrNames(clazz)) {
if(!columns.contains(attr)) {
columns.add(attr);
}
}
Object attrValue=null;
T object=null;
while(rs.next()) {
for(String attr:columns) {
try {
attrValue=rs.getObject(attr);
map.put(attr, attrValue);
} catch (Exception e) {
try {
Class<?> attrClass=Class.forName(BaseEntityPacage+"."+attr.substring(0, 1).toUpperCase()+attr.substring(1));
T attr_Object=null;
Map<String,Object> attr_Object_attrs=new HashMap<String,Object>();
for(String attr_attr:BuildObjectUtil.getAttrNames(attrClass)) {
Object attr_attr_value=null;
try {
attr_attr_value=rs.getObject(attr_attr);
} catch (Exception e2) {
continue;
}
if(attr_attr_value==null) {
continue;
}
attr_Object_attrs.put(attr_attr, attr_attr_value);
}
attr_Object=BuildObjectUtil.build(attrClass, attr_Object_attrs);
map.put(attr, attr_Object);
} catch (Exception e1) {
System.out.println("SQL查询：获取列"+attr+"失败");
}
}
}
object=BuildObjectUtil.build(clazz, map);
if(object!=null) {
objects.add(object);
}
map.clear();
}
} catch (Exception e) {
System.err.println("执行Sql出现错误：sql="+sql+" &|&msg="+e.getMessage());
}
return objects;
}
public static <E> int executeInsert(String tableName,E object) {
String sql="insert into "+tableName;
String col="";
String row="";
Map<String,Object> element=ResolveObjectUtil.resolveObject(object);
for(String key:element.keySet()) {
Object value=element.get(key);
if(value==null) {
continue;
}
String className=value.getClass().getName();
if(!className.startsWith("java.lang")&&!className.startsWith("java.math")&&!className.equals("java.util.Date")) {
continue;
}
col=col+","+key;
row+=",";
if("java.lang.String".equals(className)) {
row=row+"'"+element.get(key)+"'";
}else {
Object value1=element.get(key);
if(className.equals("java.util.Date")) {
row=row+"'"+sdf.format(value1)+"'";
}else {
row=row+element.get(key);
}
}
}
sql=sql+"("+col.substring(1)+") values("+row.substring(1)+")";
return SqlHelper.executeNoQuery(sql);
}
public static <E> int executeInsert(String tableName,List<E> objects) {
String sql="insert into "+tableName;
String col="";
String crow="";
boolean getedAttrNames=false;
List<Map<String,Object>> elements=ResolveObjectUtil.resolveObjects(objects);
for(Map<String,Object> element:elements) {
String row="";
for(String key:element.keySet()) {
Object value=element.get(key);
if(value==null) {
continue;
}
String className=value.getClass().getName();
if(!className.startsWith("java.lang")&&!className.startsWith("java.math")) {
continue;
}
if(!getedAttrNames) {
col=col+","+key;
}
row+=",";
if("java.lang.String".equals(className)) {
row=row+"'"+element.get(key)+"'";
}else {
row=row+element.get(key);
}
}
if(!getedAttrNames) {
sql=sql+"("+col.substring(1)+")";
getedAttrNames=true;
}
crow=crow+",("+row.substring(1)+")";
}
sql=sql+" values"+crow.substring(1)+";";
return SqlHelper.executeNoQuery(sql);
}
/**
* 执行更新操作
* @param tableName 表名
* @param object 数据对象
* @param whereCause SQL的"Where id=xx"等语句，开头不需要空格
* @return
*/
public static <E> int executeUpdate(String tableName,E object,String whereCause) {
String sql="update "+tableName+" set ";
String sets="";
Map<String,Object> element=ResolveObjectUtil.resolveObject(object);
String prikey=getPrimaryKeyFromSqlWhere(whereCause);
if(prikey!=null&&!"".equals(prikey)) {
element.remove(prikey);
}
for(String key:element.keySet()) {
Object value=element.get(key);
if(value==null) {
continue;
}
String className=value.getClass().getName();
if(!className.startsWith("java.lang")&&!className.startsWith("java.math")) {
continue;
}
sets=sets+","+key+"=";
if("java.lang.String".equals(className)) {
sets=sets+"'"+element.get(key)+"'";
}else {
sets=sets+element.get(key);
}
}
sql=sql+sets.substring(1)+" "+whereCause;
return SqlHelper.executeNoQuery(sql);
}
private static List<String> getColumnsFromSql(String sql){
List<String> list=new ArrayList<String>();
if(sql==null) {
return null;
}
sql=sql.toLowerCase();
int selectEndIndex=sql.indexOf("select")+6;
int fromStartIndex=sql.indexOf("from")-1;
String columnString=sql.substring(selectEndIndex, fromStartIndex).trim();
String[] columnStrs=columnString.split(",");
for(String columnStr:columnStrs) {
if(columnStr.contains("*")) {
continue;
}
int whitespaceIndex=columnStr.trim().indexOf(" ");
if(whitespaceIndex>0) {
columnStr=columnStr.substring(whitespaceIndex+1).trim();
}
int pointIndex=columnStr.trim().indexOf(".");
if(pointIndex>0) {
columnStr=columnStr.substring(pointIndex+1).trim();
}
list.add(columnStr);
}
return list;
}
private static String getPrimaryKeyFromSqlWhere(String whereCase) {
String whereCase_LowerCase=whereCase.toLowerCase().trim();
int whereStrIndex=whereCase_LowerCase.indexOf("where")+6;
int equalsStrIndex=whereCase_LowerCase.indexOf("=");
String key=whereCase.substring(whereStrIndex, equalsStrIndex);
return key;
}
}
用法如下 ：
前提条件：
1.把类（DbConnection、BuildObjectUtil、ResolveObjectUtil、SqlHelper）复制到自己的项目内，做数据库连接修改
2.类的属性名要与数据库字段一致，且均为驼峰命名法，例如数据库表为T_User，字段为：
实体类为：
package web.entity;
public class User {
private Integer vipId;
private String vipName;
private String password;
private Integer communityId;
private Community community;
private String vipPhone;
private Integer aBalance;
private String isLead;
private String address;
public User() {}
public User(String vipName, String password, String vipPhone, String address) {
super();
/*this.vipId = vipId;*/
this.vipName = vipName;
this.password = password;
this.vipPhone = vipPhone;
this.address = address;
}
/**
* 下面省略 get方法、set方法...
*/
}
接口实现：
Dao层接口
package web.dao;
import java.util.List;
import web.entity.User;
public interface IUserDao {
/**插入用户信息*/
public int insertUser(User User);
/**通过主键删除用户信息*/
public int deleteUser(Integer vipId);
/** 执行更新用户信息*/
public int updateUser(User User);
/**查询所有用户信息*/
public List<User> selectUser();
/**通过主键查询单个用户信息*/
public User selectUser(Integer vipId);
}Dao层的实现类：
package web.dao.impl;
import java.util.List;
import web.dao.IUserDao;
import web.dao.util.SqlHelper;
import web.entity.User;
/**用户信息数据层*/
public class UserDaoImpl implements IUserDao{
private static final String TABLENAME = "T_User";
/**插入用户信息*/
public int insertUser(User User) {
return SqlHelper.executeInsert(TABLENAME, User);
}
/**通过主键删除用户信息*/
@Override
public int deleteUser(Integer vipId) {
return SqlHelper.executeNoQuery("delete from " + TABLENAME + " where vipId=" + vipId);
}
/**执行更新用户信息*/
@Override
public int updateUser(User User) {
return SqlHelper.executeUpdate(TABLENAME, User, "where vipId=" + User.getVipId());
}
/**查询所有用户信息*/
@Override
public List<User> selectUser() {
return SqlHelper.executeQuery(User.class, "select * from " + TABLENAME);
}
/**通过主键查询单个用户信息*/
@Override
public User selectUser(Integer vipId) {
return SqlHelper.executeQueryOne(User.class,
"select * from " + TABLENAME + " where vipId=" + vipId);
}
}
接下来，就是根据自己的项目要求，进行业务层的编写了。。
已知bug：
1. Sql语句中 -> 在where条件语句内的 列 会被忽略更新，解决办法：让where内只有主键（where id=xx）
总结：
学生党可以用(*^▽^*)，小型的、要求不是特别严格的Java程序可以这么用，商业的应用级还是妥妥的用框架吧。
5329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
