package com.huiyun.common.dao;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.ServletRequest;
import org.junit.Test;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ibm.icu.text.SimpleDateFormat;
import com.tis.helper.SqlM;
public class CommonDao {
static SimpleDateFormat ymdhms = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static JSONObject getById(String tableName, String pkName,Serializable id) {
String sql="select * from ["+tableName+"] where "+pkName+"=?";
List<Object> param=new ArrayList<Object>();
JSONArray ja= excuteQuery(sql,param);
if(ja.size()>0)
return ja.getJSONObject(0);
else
return new JSONObject();
}
public static JSONArray excuteQuery(String sql, List<Object> param) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement(sql);
for (int i = 0; param != null && i < param.size(); i++) {
Object value = param.get(i);
if (value == null) {
pstmt.setNull(i + 1, Types.OTHER);
} else if(value instanceof Date) {
String datestr = ymdhms.format((java.util.Date) value);
pstmt.setString(i + 1, datestr);
} else {
pstmt.setObject(i + 1, value);
}
}
rs = pstmt.executeQuery();
return rs2JSONObject(rs);
} catch (SQLException e) {
e.printStackTrace();
return new JSONArray();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
}
public static boolean exists(String sql, List<Object> param) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement(sql);
for (int i = 0; param != null && i < param.size(); i++) {
Object value = param.get(i);
if (value == null) {
pstmt.setNull(i + 1, Types.OTHER);
} else if(value instanceof Date) {
String datestr = ymdhms.format((java.util.Date) value);
pstmt.setString(i + 1, datestr);
}else {
pstmt.setObject(i + 1, value);
}
}
rs = pstmt.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
}
public static JSONArray rs2JSONObject(ResultSet rs) {
try {
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
List<String> names = new ArrayList<String>();
for (int i = 0; i < cols; i++) {
names.add(rsmd.getColumnLabel(i + 1));
}
JSONArray ja = new JSONArray();
while (rs.next()) {
JSONObject jo = new JSONObject();
for (String name : names) {
jo.put(name, rs.getObject(name));
}
ja.add(jo);
}
return ja;
} catch (SQLException e) {
e.printStackTrace();
return new JSONArray();
}
}
public static int update(String tableName, JSONObject jo, String pkName) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("update ").append(tableName)
.append(" set ");
Set<String> keys = jo.keySet();
boolean isFirst = true;
List<Object> param = new ArrayList<Object>();
for (String key : keys) {
if (jo.get(key) != null && !key.equalsIgnoreCase(pkName)) {
if (isFirst) {
sql.append(" [").append(key).append("]=?");
isFirst = false;
} else {
sql.append(" ,[").append(key).append("]=? ");
}
param.add(jo.get(key));
}
}
sql.append(" where [").append(pkName).append("]=?");
param.add(jo.get(pkName));
System.out.println(sql);
System.out.println(JSON.toJSONStringWithDateFormat(param,
"yyyy-MM-dd HH:mm:ss"));
try {
pstmt = con.prepareStatement(sql.toString());
int index = 1;
for (Object o : param) {
if (o instanceof java.util.Date) {
// long time=((java.util.Date)o).getTime();
// javax.sql.Date d=new javax.sql.Date(time);
// pstmt.setDate(index, d);
String datestr = ymdhms.format((java.util.Date) o);
pstmt.setString(index, datestr);
index++;
} else {
pstmt.setObject(index, o);
index++;
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(sql);
System.out.println(jo.toJSONString());
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
return -1;
}
public static boolean isExist(String name,String...pkNames){
for(String pkName:pkNames){
if(name.equalsIgnoreCase(pkName))
return true;
}
return false;
}
public static int update(String tableName, JSONObject jo, String... pkNames) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("update ").append(tableName)
.append(" set ");
Set<String> keys = jo.keySet();
boolean isFirst = true;
List<Object> param = new ArrayList<Object>();
for (String key : keys) {
if (jo.get(key) != null && !isExist(key,pkNames)) {
if (isFirst) {
sql.append(" [").append(key).append("]=?");
isFirst = false;
} else {
sql.append(" ,[").append(key).append("]=? ");
}
param.add(jo.get(key));
}
}
sql.append(" where ");
isFirst = true;
for(String pkName:pkNames){
if(isFirst){
sql.append(" [").append(pkName).append("]=?");
param.add(jo.get(pkName));
isFirst=false;
}else{
sql.append(" and [").append(pkName).append("]=?");
param.add(jo.get(pkName));
}
}
System.out.println(sql);
System.out.println(JSON.toJSONStringWithDateFormat(param,
"yyyy-MM-dd HH:mm:ss"));
try {
pstmt = con.prepareStatement(sql.toString());
int index = 1;
for (Object o : param) {
if (o instanceof java.util.Date) {
// long time=((java.util.Date)o).getTime();
// javax.sql.Date d=new javax.sql.Date(time);
// pstmt.setDate(index, d);
String datestr = ymdhms.format((java.util.Date) o);
pstmt.setString(index, datestr);
index++;
} else {
pstmt.setObject(index, o);
index++;
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(sql);
System.out.println(jo.toJSONString());
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
return -1;
}
public static int insert(String tableName, JSONObject jo) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("insert into [").append(
tableName.trim()).append("] (");
Set<String> keys = jo.keySet();
boolean isFirst = true;
List<Object> param = new ArrayList<Object>();
StringBuffer vsql = new StringBuffer(" values(");
for (String key : keys) {
if (jo.get(key) != null) {
if (isFirst) {
sql.append("[").append(key).append("]");
vsql.append("?");
isFirst = false;
} else {
sql.append(" ,[").append(key).append("]");
vsql.append(" ,?");
}
param.add(jo.get(key));
}
}
sql.append(" ) ");
vsql.append(" ) ");
System.out.println(sql.append(vsql));
try {
pstmt = con.prepareStatement(sql.toString());
int index = 1;
for (String key : keys) {
Object o = jo.get(key);
if (o != null) {
if (o instanceof java.util.Date) {
// long time=((java.util.Date)o).getTime();
// javax.sql.Date d=new javax.sql.Date(time);
// pstmt.setDate(index, d);
String datestr = ymdhms.format((java.util.Date) o);
pstmt.setString(index, datestr);
index++;
} else {
pstmt.setObject(index, o);
index++;
}
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(sql);
System.out.println(jo.toJSONString());
}finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
return -1;
}
public static int update(String tableName, ServletRequest request,
String pkName) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("update ").append(tableName)
.append(" set ");
Enumeration<String> ekeys = request.getParameterNames();
Set<String> keys = new HashSet<String>();
while (ekeys.hasMoreElements()) {
keys.add(ekeys.nextElement());
}
boolean isFirst = true;
List<Object> param = new ArrayList<Object>();
for (String key : keys) {
if (request.getParameter(key) != null
&& !key.equalsIgnoreCase(pkName)) {
if (isFirst) {
sql.append(" [").append(key).append("]=?");
isFirst = false;
} else {
sql.append(" ,[").append(key).append("]=? ");
}
param.add(request.getParameter(key));
}
}
sql.append(" where [").append(pkName).append("]=?");
param.add(request.getParameter(pkName));
System.out.println(sql);
System.out.println(JSON.toJSONStringWithDateFormat(param,
"yyyy-MM-dd HH:mm:ss"));
try {
pstmt = con.prepareStatement(sql.toString());
int index = 1;
for (Object o : param) {
pstmt.setObject(index, o);
index++;
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(sql);
}finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
return -1;
}
public static int insert(String tableName, ServletRequest request) {
SqlM sqlM = new SqlM();
Connection con = sqlM.conn;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("insert into [").append(
tableName.trim()).append("] (");
Enumeration<String> ekeys = request.getParameterNames();
Set<String> keys = new HashSet<String>();
while (ekeys.hasMoreElements()) {
keys.add(ekeys.nextElement());
}
boolean isFirst = true;
List<Object> param = new ArrayList<Object>();
StringBuffer vsql = new StringBuffer(" values(");
for (String key : keys) {
if (request.getParameter(key) != null) {
if (isFirst) {
sql.append("[").append(key).append("]");
vsql.append("?");
isFirst = false;
} else {
sql.append(" ,[").append(key).append("]");
vsql.append(" ,?");
}
param.add(request.getParameter(key));
}
}
sql.append(" ) ");
vsql.append(" ) ");
System.out.println(sql.append(vsql));
try {
pstmt = con.prepareStatement(sql.toString());
int index = 1;
for (String key : keys) {
if (request.getParameter(key) != null) {
pstmt.setObject(index, request.getParameter(key));
index++;
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(sql);
}finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
sqlM.closeConnection();
}
}
return -1;
}
@Test
public void test() {
List<Object> list=new ArrayList<Object>();
list.add(1);
JSONArray ja = excuteQuery("select * from dw_sale_bill where memberId=?", list);
System.out.println(JSON.toJSONStringWithDateFormat(ja,
"yyyy-MM-dd HH:mm:ss"));
}
@Test
public void qgc_test() {
JSONObject jo = new JSONObject();
jo.put("name", "name");
jo.put("desc", "desc");
jo.put("bfloatb", 4.0f);
jo.put("bintb", 4);
jo.put("cdate", new Date());
insert("qgc_test", jo);
jo.put("name", "name2");
jo.put("desc", "desc2");
jo.put("bfloatb", 5.0f);
jo.put("bintb", 5);
jo.put("id", 2);
//jo.put("ts", new Date());
update("qgc_test", jo, "id");
}
public static JSONObject request2JSONObject(ServletRequest request,String... names){
JSONObject jo=new JSONObject();
for(String name:names){
String value=request.getParameter(name);
jo.put(name, value);
}
return jo;
}
public static List<JSONObject> request2JSONObjects(ServletRequest request,String... names){
List<JSONObject> list=new ArrayList<JSONObject>();
Map<String, String[]> map = request.getParameterMap();
int max=0;
for(String name:names){
String[] values=map.get(name);
if(values!=null&&max<values.length){
max=values.length;
}
}
for(int i=0;i<max;i++){
JSONObject jo=new JSONObject();
for(String name:names){
String[] values=map.get(name);
if(i<values.length){
jo.put(name, values[i]);
}
}
}
return list;
}
static int max(int... v){
int max=0;
for(int i=0;i<v.length;i++){
if(v[i]>max)
max=v[i];
}
return max;
}
}
相关推荐
实现完全的对jdbc对象的封装，只需调用则可对数据库进行CRUD操作
java增删改查JDBC封装类(泛型封装),这个是高度封装化的，增删改查，直接用到我的项目中去的。
包括普通的增删查改，多条数据的增删改并封装成事务，结果返回到对象集合和list
将数据库连接和增删查改的方法进行封装，将重复性的代码提取出去作为工具类，尽量减少类与类之间的固定依赖
MyBatis框架由Java的JDBC API进一步封装而来,在操作数据库方面效果拔群,接下来我们就一起来看看Java通过MyBatis框架对MySQL数据进行增删查改的基本方法:
通过以上最基本的技术，不做任何封装的实现一个系统，（例如登录、增删查改）
是一个练习ssh框架整合的入门项目，同时可以熟悉hibernate进行增删查改的操作。 技术上的提升： 在web层和dao层都提取了公共代码封装到BaseAction和BaseDao中，提高了代码的复用，同时按照OO设计原则，针对接口...
是一个练习ssh框架整合的入门项目，同时可以对熟悉的hibernate进行增删查改的操作。 技术上的提升： 在web层和dao层都提取了公共代码封装到BaseAction和BaseDao中，提高了代码的复用性，同时遵循OO设计原则，接口...
这是我2017.11.29写的JDBC学习笔记里面详细介绍JDBC的连接到Mysql的代码以及截图步骤，里面覆盖了封装的代码，包括增删查改等基本数据操作，jdbc驱动的下载连接
1.在mysql中创建一个Person...2. 创建Person的pojo、Dao接口及实现类，Dao中有增删查改方法。创建切面类，里面有身份核查、日志记录（记录在日志表中）。选择合适的连接点，使用xml声明式和注解两种方式完成aop的配置。
该uml时序图是接上一博文jabc的连接封装，描述了里面的增删查改等时序
并封装了常用的增删查改的方法。 代码示例 private static final String driver="com.mysql.jdbc.Driver"; //"jdbc:mysql://127.0.0.1:3306/数据库名称" private static final String url="jdbc:mysql://...
java的basedao初步封装jdbc，实现数据的增删查改的功能
通过对象实现数据基本增删查改操作，最好可以扩展丰富的查询功能 封装后要求使用方便，不暴露与数据库相关的操作 配置信息简洁（最好只包含连接数据库相关的配置），尽量不用XML配置 最好有默认配置，有抽象默认基本...