[一百分求解答]实际的情况下java如何操作数据库? 来学习下,不管怎么样,sql语句还是得自己写吧? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 楼主在自学JAVA ing?1.传统的原始开发模式2.MVC开发模式: SSH(Spring+stauts+hibernate)/SSI(Spring+stauts+ibatis)二种主流集成框架方式: 楼主所说的通过工具生成实体bean,就是hibernate框架做的事,每张表映射生成一个实体bean,项目层次: 分action层->service层->dao层 action是: 响应前台事件,取得前台封装过来的数据; service层:业务逻辑层; dao层:写SQL或HQL,调用数据连接类的CRUD方法和数据库交互;PS:我写了个大概,发现要写的东西实在太多了····楼主可以通过我写的关键字去找资料了解下,比我说来的好 可以使用BeanUtils将取出的数据封装到对应的javabean 我们用的JDNI啊,直接改下配置文件就行了 我们现在做的项目中,已经很少使用hibernate,就连strust都没用。直接用springMVC+springJDBC,用spring的jdbc就可以实现实体类的绑定,至于实体类,楼主已经有工具生成,那就省事了,直接用spring的JDBC即可,举一个crud的例子:就比如实体类 Bean,,查询(根据Id查询):public Bean getBean(Integer id) { StringBuilder selectBuilder = new StringBuilder(); selectBuilder.append(" SELECT * from xxx where id=?"); return new SimpleJdbcTemplate().getSimpleJdbcTemplate().queryForObject(selectBuilder.toString(), new ParameterizedRowMapper<Bean>() { public Bean mapRow(ResultSet rs, int rowNum) throws SQLException { Bean bean = new Bean (); bean.setId(rs.getInt("id")); bean.xxx(rs.getString("xxx")); //...设置各个需要存入实体的值 return bean; } }, id); } 顶楼上 的beanUtils apache commons jar包内 非常实用的工具 蹭个分 一般用ORM框架可以减少你写sql的麻烦。比如morphia,直接写pojo对象,对象属性设置上,调用对象的save方法即可保存到数据库。还有你这零件的字段这么长??是否可以优化成多个表关联呢? 多谢。1条数据,或是几条数据,可以生成一个bean。那如果一下找出来几十万条数据,也是要挨个生成bean么? 多谢。1条数据,或是几条数据,可以生成一个bean。那如果一下找出来几十万条数据,也是要挨个生成bean么?楼主,查几十万条数据那也只是交给了数据库去做,我们可以考虑加上分页,每次只找出10条,20条,或者50条等等,但是,如果是涉及到导出,可以另做一个直接用jdbc的方式进行查询,跳过bean绑定。 多谢。1条数据,或是几条数据,可以生成一个bean。那如果一下找出来几十万条数据,也是要挨个生成bean么?楼主,查几十万条数据那也只是交给了数据库去做,我们可以考虑加上分页,每次只找出10条,20条,或者50条等等,但是,如果是涉及到导出,可以另做一个直接用jdbc的方式进行查询,跳过bean绑定。明白了,多谢了 给LZ提供一些连接吧,这些都是挺不错的!http://blog.csdn.net/xiehuan_uestc/article/details/7969321http://www.cnblogs.com/Caceolod/articles/1280258.htmlhttp://blog.csdn.net/csh624366188/article/details/7305672http://www.cnblogs.com/baiweiguo/archive/2013/02/21/2920638.html其实这样的连接还有很多,而且总结的都挺全面的,我只是来蹭分的 给楼主我的生成bean代码:import java.io.FileWriter;import java.io.IOException;import java.io.PrintWriter;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSetMetaData;import java.sql.SQLException;import javax.sql.DataSource;import org.springframework.jdbc.datasource.DataSourceUtils;import com.wasion.framework.service.ServiceLocator;/** * 数据库表转换成javaBean对象小工具, * bean属性按原始数据库字段经过去掉下划线,并大写处理首字母等等. * */public class MyEntityUtils { private String tablename = ""; private String[] colnames; private String[] colTypes; private int[] colSizes; // 列名大小 private int[] colScale; // 列名小数精度 private boolean importUtil = false; // 导入包java.util.* private boolean importSql = false; // 包java.sql.* private boolean importMath = false; // 包java.sql.* private boolean useObjectType = false; // bean类中使用基本类型还是对象类型 /** * @param args */ public void tableToEntity(String tName) { tablename = tName; //数据连Connection获取,自己想办法就行. DataSource ds = (DataSource) ServiceLocator.getService("dataSource"); Connection conn = DataSourceUtils.getConnection(ds); // 得到数据库连接 String strsql = "SELECT * FROM " + tablename;//+" WHERE ROWNUM=1"; try { System.out.println(strsql); PreparedStatement pstmt = conn.prepareStatement(strsql); pstmt.executeQuery(); ResultSetMetaData rsmd = pstmt.getMetaData(); int size = rsmd.getColumnCount(); // 共有多少列 colnames = new String[size]; colTypes = new String[size]; colSizes = new int[size]; colScale = new int[size]; for (int i = 0; i < rsmd.getColumnCount(); i++) { rsmd.getCatalogName(i + 1); colnames[i] = rsmd.getColumnName(i + 1).toLowerCase(); colTypes[i] = rsmd.getColumnTypeName(i + 1).toLowerCase(); colScale[i] = rsmd.getScale(i + 1); System.out.println(rsmd.getCatalogName(i+1)); if ("datetime".equals(colTypes[i])) { importUtil = true; } if ("image".equals(colTypes[i]) || "text".equals(colTypes[i])) { importSql = true; } if(colScale[i]>0){ importMath = true; } colSizes[i] = rsmd.getPrecision(i + 1); } String content = parse(colnames, colTypes, colSizes); try { FileWriter fw = new FileWriter(initcap(tablename) + ".java"); PrintWriter pw = new PrintWriter(fw); pw.println(content); pw.flush(); pw.close(); } catch (IOException e) { e.printStackTrace(); } } catch (SQLException e) { e.printStackTrace(); } finally { DataSourceUtils.releaseConnection(conn, ds); } } /** * 解析处理(生成实体类主体代码) */ private String parse(String[] colNames, String[] colTypes, int[] colSizes) { StringBuffer sb = new StringBuffer(); sb.append("\r\nimport java.io.Serializable;\r\n"); if (importUtil) { sb.append("import java.util.Date;\r\n"); } if (importSql) { sb.append("import java.sql.*;\r\n\r\n"); } if(importMath){ sb.append("import java.math.*;\r\n\r\n"); } //表注释 processColnames(sb); sb.append("public class " + initcap(tablename) + " implements Serializable {\r\n"); processAllAttrs(sb); processAllMethod(sb); sb.append("}\r\n"); System.out.println(sb.toString()); return sb.toString(); } /** * 处理列名,把空格下划线'_'去掉,同时把下划线后的首字母大写 * 要是整个列在3个字符及以内,则去掉'_'后,不把"_"后首字母大写. * 同时把数据库列名,列类型写到注释中以便查看, * @param sb */ private void processColnames(StringBuffer sb) { sb.append("\r\n/** " + tablename + "\r\n"); String colsiz=""; String colsca=""; for (int i = 0; i < colnames.length; i++) { colsiz = colSizes[i]<=0? "" : (colScale[i]<=0? "("+colSizes[i]+")" : "("+colSizes[i]+","+colScale[i]+")"); sb.append("\t" + colnames[i].toUpperCase() +" "+colTypes[i].toUpperCase()+ colsiz+"\r\n"); char[] ch = colnames[i].toCharArray(); char c ='a'; if(ch.length>3){ for(int j=0;j <ch.length; j++){ c = ch[j]; if(c == '_'){ if (ch[j+1]>= 'a' && ch[j+1] <= 'z') { ch[j+1]=(char) (ch[j+1]-32); } } } } String str = new String(ch); colnames[i] = str.replaceAll("_", ""); } sb.append("*/\r\n"); } /** * 生成所有的方法 * * @param sb */ private void processAllMethod(StringBuffer sb) { for (int i = 0; i < colnames.length; i++) { sb.append("\tpublic void set" + initcap(colnames[i]) + "(" + oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " " + colnames[i] + "){\r\n"); sb.append("\t\tthis." + colnames[i] + "=" + colnames[i] + ";\r\n"); sb.append("\t}\r\n"); sb.append("\tpublic " + oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " get" + initcap(colnames[i]) + "(){\r\n"); sb.append("\t\treturn " + colnames[i] + ";\r\n"); sb.append("\t}\r\n"); } } /** * 解析输出属性 * * @return */ private void processAllAttrs(StringBuffer sb) { sb.append("\tprivate static final long serialVersionUID = 1L;\r\n"); for (int i = 0; i < colnames.length; i++) { sb.append("\tprivate " + oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " " + colnames[i] + ";\r\n"); } sb.append("\r\n"); } /** * 把输入字符串的首字母改成大写 * @param str * @return */ private String initcap(String str) { char[] ch = str.toCharArray(); if (ch[0] >= 'a' && ch[0] <= 'z') { ch[0] = (char) (ch[0] - 32); } return new String(ch); } /** * Oracle * @param sqlType * @param scale * @return */ private String oracleSqlType2JavaType(String sqlType, int scale,int size) { if (sqlType.equals("integer")) { return "Integer"; } else if (sqlType.equals("long")) { return "Long"; } else if (sqlType.equals("float")) { return "BigDecimal"; } else if (sqlType.equals("double precision")) { return "BigDecimal"; } else if (sqlType.equals("number") ||sqlType.equals("decimal") || sqlType.equals("numeric") || sqlType.equals("real")) { return scale==0? (size<10? "Integer" : "Long") : "BigDecimal"; }else if (sqlType.equals("varchar") || sqlType.equals("varchar2") || sqlType.equals("char") || sqlType.equals("nvarchar") || sqlType.equals("nchar")) { return "String"; } else if (sqlType.equals("datetime") || sqlType.equals("date") || sqlType.equals("timestamp")) { return "Date"; } return null; } /** * @param args */ public static void main(String[] args) { MyEntityUtils t = new MyEntityUtils(); t.tableToEntity("TABLE_NAME"); }} 我还有一次生成所有代码的程序,生成 bean生成 service接口,service实现生成 dao接口,dao实现自动实现 1增加,2删除,3修改,4按主键查询,5按给定的where条件查询, 6批理增加,7批删除,8批修改, 9批修改或批增加.9大方法,不过我只能提示楼主,多写代码,程序员就是写代码的,这些要自己去做. 想问一下大家,难道大家在 jdbc查数据时 填充bean时都是手工写的代码? 天拉, insert, select, update 那个眼都看花!! 这个做个工具就行了代码全部自动生成, 这都是做一次永远使用的代码, 程序员重要是写业务逻辑代码,数据库的代码都应当不要写,或者尽量少写. 哦,这样啊,请问有什么现成的工具么?我学习学习。我以前是用PB的,从来没有写过sql语句,用了java,还以为全要自己写 我创建一个表,生成样本代码给你看, 上面生成bean自己扩展就能做到,呵呵. ----===============================----建一个表----===============================CREATE TABLE MY_TABLE ( MYID NUMBER(10) NOT NULL, MYTYPE NUMBER(6) NOT NULL, MYNAME VARCHAR2(32), MYDATE DATE, REMARK VARCHAR2(64) ,CONSTRAINT MY_TABLE PRIMARY KEY (MYID)); 多谢。bean我已经能够生成,请问能不能给个例子,我学学doa如何生成?谢谢。 dao生成的代码好长,我也是在bean工具上做的, 我把生成的代码给你看,以便你开始可以用java开发数据库,并且这是成熟的代码, 对了生成的模板都是 spring (集成spring很简单,网上很多),连接池用 apache的 dbcp包, 不要用什么 hibernate等其它 orm工具 这种东西我在多年的工作中显示他们不适合大型项目,我的项目中表都是上 10亿-100亿行数据, 我想你的项目也可以.生成样本第1段import java.io.Serializable;import java.sql.Connection;import java.sql.Statement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Timestamp;import java.util.*;import java.math.*;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.springframework.jdbc.core.support.JdbcDaoSupport;import org.springframework.jdbc.datasource.DataSourceUtils;import org.springframework.jdbc.support.JdbcUtils;//此工具自己做,或者自己手工改jdbc原始方法import com.my.util.JdbcMyUtils;/** * MY_TABLE * @author table tool 2013-12-30 */public class MyTableDaoJdbcImpl extends JdbcDaoSupport implements IMyTableDao { private Log logger = LogFactory.getLog(this.getClass()); private JdbcMyUtils jtool = new JdbcMyUtils(); private String table = "MY_TABLE"; private String name = "MY_TABLE"; private final int BATCH_SIZE=500; /** findAll row */ public List<MyTable> findAll(String sqlExpression) { if(sqlExpression==null) return null; String tablename = null; String strSql = null; List<MyTable> list = new ArrayList<MyTable>(); tablename = table; strSql =" SELECT "+this.getColumnString()+" FROM "+ tablename+" "+sqlExpression; //call Template this.findEntityTemplate(list, strSql); return list; } /** findAll row */ public List<MyTable> findAll() { String tablename = null; String strSql = null; List<MyTable> list = new ArrayList<MyTable>(); tablename = table; strSql =" SELECT "+this.getColumnString()+" FROM "+ tablename; //call Template this.findEntityTemplate(list, strSql); return list; } /** isExist row */ public boolean isExist(Long arg0){ if(arg0==null) return false; String tablename = null; String strSql = null; boolean boolReturn = false; tablename = table;// + MonTableUtils.monthTable(dataMonth, table); strSql =" SELECT COUNT(0) FROM "+ tablename+" WHERE ID="+arg0; //TODO ADD WHERE CODE...... //call Template int rows = this.updateSqlTemplate(strSql); if(rows>0) boolReturn = true; return boolReturn; } /** read row */ public MyTable read(Long arg0) { if(arg0==null) return null; String tablename = null; String strSql = null; MyTable e = null; tablename = table; strSql = "SELECT "+this.getColumnString()+" FROM "+tablename+" WHERE ID="+arg0; //TODO ADD WHERE CODE...... //call Template e = findEntityTemplate(strSql); return e; } /** create row */ public Long create(MyTable entity) { if(entity==null) return null; String tablename = null; String strSql = null; boolean boolReturn = false; Long id=0L; //entity id; tablename = table; strSql =" INSERT INTO "+ tablename +" ("+this.getColumnString()+") "+ " VALUES(?,?,?,?,?)"; //call Template boolReturn = insertEntityTemplate(entity, strSql); return boolReturn ? id : null; } /** delete row */ public boolean delete(MyTable entity) { if(entity==null) return false; String tablename = null; String strSql = null; boolean boolReturn = false; tablename = table; strSql =" DELETE "+ tablename + " WHERE ID=1..... "; //TODO ADD WHERE CODE...... //call Template boolReturn = deleteEntityTemplate(entity, strSql); return boolReturn; } /** delete row */ public boolean delete(Long arg0) { if(arg0==null) return false; String tablename = null; String strSql = null; boolean boolReturn = false; tablename = table; strSql =" DELETE "+ tablename + " WHERE ID=1..... "; //TODO ADD WHERE CODE...... //call Template int rows = this.updateSqlTemplate(strSql); if(rows>=0) boolReturn = true; return boolReturn; } /** update row */ public boolean update(MyTable entity) { if(entity==null) return false; String tablename = null; String strSql = null; boolean boolReturn = false; tablename = table; strSql =" UPDATE "+ tablename +" SET"+ " MYID=?,MYTYPE=?,MYNAME=?,MYDATE=?,REMARK=?"+ " WHERE ID=1..... "; //TODO ADD WHERE CODE...... //call Template boolReturn = updateEntityTemplate(entity, strSql); return boolReturn; } 生成样本第2段 //-------------------------Template CODE------------------------- /** insert,delete,update Sql Template * UPDATE,DELETE RETURN >=0 SUCCEED, INSERT >0 SUCCEED; * JDBC:0-NO DATA, 1-SUCCEED, -1-FAIL. */ private int updateSqlTemplate(String strSql){ if(strSql==null) return 0; int rows=0; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); rows = this.getJdbcTemplate().update(strSql); } catch (Exception ex) { logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; } return rows; } /** insert,delete,update Sql Template * UPDATE,DELETE RETURN >=0 SUCCEED, INSERT >0 SUCCEED; * JDBC:0-NO DATA, 1-SUCCEED, -1-FAIL. */ private int updateSqlTemplate(String strSql, Object[] args){ if(strSql==null) return 0; int rows=0; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); rows = this.getJdbcTemplate().update(strSql, args); } catch (Exception ex) { logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; } return rows; } /** insertTemplate */ private boolean insertEntityTemplate(MyTable entity, String strSql){ if(entity==null) return false; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; int rows=0; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); pst = con.prepareStatement(strSql); this.processInsertRow(pst, entity); rows = pst.executeUpdate(); // INSERT SQL RETURN >0 SUCCEED if(rows>0) boolReturn = true; } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; JdbcUtils.closeStatement(pst); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** deleteTemplate */ private boolean deleteEntityTemplate(MyTable entity, String strSql){ if(entity==null) return false; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; int rows=0; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); pst = con.prepareStatement(strSql); this.processDeleteRow(pst, entity); rows = pst.executeUpdate(); // DELETE SQL RETURN >=0 SUCCEED (=0 NO DATA) if(rows>=0) boolReturn = true; } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; JdbcUtils.closeStatement(pst); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** updataTemplate */ private boolean updateEntityTemplate(MyTable entity, String strSql){ if(entity==null) return false; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; int rows=0; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); pst = con.prepareStatement(strSql); this.processUpdateRow(pst, entity); rows = pst.executeUpdate(); // UPDATE SQL RETURN >=0 SUCCEED (=0 NO DATA) if(rows>=0) boolReturn = true; } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; JdbcUtils.closeStatement(pst); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** findEntityTemplate one entity */ private MyTable findEntityTemplate(String strSql){ Connection con = null; PreparedStatement pst = null; ResultSet rs = null; MyTable e=null; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); pst = con.prepareStatement(strSql); rs = pst.executeQuery(); e =this.processRow(rs); } catch (Exception ex) { logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; JdbcUtils.closeStatement(pst); JdbcUtils.closeResultSet(rs); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return e; } /** findEntityTemplate list entity */ private void findEntityTemplate(List<MyTable> listout, String strSql){ Connection con = null; PreparedStatement pst = null; ResultSet rs = null; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); pst = con.prepareStatement(strSql); rs = pst.executeQuery(); this.processRow(listout, rs); } catch (Exception ex) { logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; JdbcUtils.closeStatement(pst); JdbcUtils.closeResultSet(rs); DataSourceUtils.releaseConnection(con, this.getDataSource()); } } /** insertBatchTemplate */ private boolean insertBatchTemplate(List<MyTable> entities, String strSql){ if(entities==null || entities.size()==0) return true; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; MyTable entity =null; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); con.setAutoCommit(false); pst = con.prepareStatement(strSql); int i=0; for(int j=0; j<entities.size(); j++){ entity = entities.get(j); this.processInsertRow(pst, entity); pst.addBatch(); i++; if(i >= BATCH_SIZE){ if(logger.isDebugEnabled()){ logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name); } i=0; pst.executeBatch(); pst.clearBatch(); con.commit(); } } //最后把不足行的数据提交 pst.executeBatch(); pst.clearBatch(); boolReturn = true; if(logger.isDebugEnabled()){ logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name); } } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; entity = null; JdbcUtils.closeStatement(pst); jtool.commit(con, boolReturn); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** deleteBatchTemplate */ private boolean deleteBatchTemplate(List<MyTable> entities, String strSql){ if(entities==null || entities.size()==0) return true; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; MyTable entity =null; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); con.setAutoCommit(false); pst = con.prepareStatement(strSql); int i=0; for(int j=0; j<entities.size(); j++){ entity = entities.get(j); this.processDeleteRow(pst, entity); pst.addBatch(); i++; if(i >= BATCH_SIZE){ if(logger.isDebugEnabled()){ logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name); } i=0; pst.executeBatch(); pst.clearBatch(); con.commit(); } } //最后把不足行的数据提交 pst.executeBatch(); pst.clearBatch(); boolReturn = true; if(logger.isDebugEnabled()){ logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name); } } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; entity = null; JdbcUtils.closeStatement(pst); jtool.commit(con, boolReturn); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } 生成样本第3段 /** updateBatchTemplate */ private boolean updateBatchTemplate(List<MyTable> entities, String strSql){ if(entities==null || entities.size()==0) return true; boolean boolReturn = false; Connection con = null; PreparedStatement pst = null; MyTable entity =null; try { if(logger.isDebugEnabled()) logger.debug(name+": "+strSql); con = this.getConnection(); con.setAutoCommit(false); pst = con.prepareStatement(strSql); int i=0; for(int j=0; j<entities.size(); j++){ entity = entities.get(j); this.processUpdateRow(pst, entity); pst.addBatch(); i++; if(i >= BATCH_SIZE){ if(logger.isDebugEnabled()){ logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name); } i=0; pst.executeBatch(); pst.clearBatch(); con.commit(); } } //最后把不足行的数据提交 pst.executeBatch(); pst.clearBatch(); boolReturn = true; if(logger.isDebugEnabled()){ logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name); } } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+strSql, ex); } finally { strSql = null; entity = null; JdbcUtils.closeStatement(pst); jtool.commit(con, boolReturn); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** updateOrInsertBatchTemplate */ private boolean updateOrInsertBatchTemplate(List<MyTable> entities, String strUpSql, String strInSql){ if(entities==null || entities.size()==0) return true; boolean boolReturn = false; Connection con = null; PreparedStatement pstup = null; PreparedStatement pstin = null; MyTable entity =null; List<MyTable> failedData =new ArrayList<MyTable>(); int isuporin =0; int failedRow =0; int insertRow =0; int updateRow =0; int forRow =0; try { //update if(logger.isDebugEnabled()) logger.debug(name+": "+strUpSql); con = this.getConnection(); con.setAutoCommit(false); pstup = con.prepareStatement(strUpSql); int i=0; forRow = entities.size(); for(int j=0; j<forRow; j++){ entity = entities.get(j); this.processUpdateRow(pstup, entity); pstup.addBatch(); i++; if(i >= BATCH_SIZE || (j+1)==forRow){ if(logger.isDebugEnabled()){ logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name); } i=0; int[] ri =pstup.executeBatch(); pstup.clearBatch(); con.commit(); //记录失败的数据. for(int x=0;x<ri.length; x++){ if(ri[x]==Statement.EXECUTE_FAILED || ri[x]<-3){ failedData.add(entities.get(x)); } } } } //insert if(failedData.size()>0){ isuporin =1; if(logger.isDebugEnabled()) logger.debug(name+": "+strInSql); pstin = con.prepareStatement(strInSql); i=0; forRow = failedData.size(); for(int j=0; j<forRow; j++){ entity = failedData.get(j); this.processInsertRow(pstin, entity); pstin.addBatch(); i++; if(i >= BATCH_SIZE || (j+1)==forRow){ if(logger.isDebugEnabled()){ logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name); } i=0; int[] ri =pstin.executeBatch(); pstin.clearBatch(); con.commit(); //记录失败的数据. for(int x=0;x<ri.length; x++){ if(ri[x]==Statement.EXECUTE_FAILED || ri[x]<-3){ failedRow = failedRow + 1; } } } insertRow = failedData.size() - failedRow; } } updateRow = entities.size() -failedData.size(); boolReturn = true; } catch (Exception ex) { boolReturn = false; logger.error("[execute sql error]: "+(isuporin==0? strUpSql : strInSql), ex); } finally { if(logger.isDebugEnabled()) logger.info("[完成批量发送数据]: 总行"+entities.size()+", 更新"+updateRow+", 插入"+insertRow+", 失败"+failedRow+", BY "+name); entity = null; failedData.clear(); failedData =null; JdbcUtils.closeStatement(pstup); JdbcUtils.closeStatement(pstin); jtool.commit(con, boolReturn); DataSourceUtils.releaseConnection(con, this.getDataSource()); } return boolReturn; } /** Insert row */ private void processInsertRow(PreparedStatement pst, MyTable e) throws SQLException { int r = 1; jtool.setLong(pst, r++, e.getMyid()); jtool.setInt(pst, r++, e.getMytype()); jtool.setString(pst, r++, e.getMyname()); jtool.setTimestamp(pst, r++, new Timestamp(e.getMydate().getTime())); jtool.setString(pst, r++, e.getRe()); } /** Delete row */ private void processDeleteRow(PreparedStatement pst, MyTable e) throws SQLException { int r = 1; //DEMO CODE: jtool.setLong(pst, r++, e.getId()); //TODO ADD DELETE SQL WHERE CODE... } /** Update row */ private void processUpdateRow(PreparedStatement pst, MyTable e) throws SQLException { int r = 1; jtool.setLong(pst, r++, e.getMyid()); jtool.setInt(pst, r++, e.getMytype()); jtool.setString(pst, r++, e.getMyname()); jtool.setTimestamp(pst, r++, new Timestamp(e.getMydate().getTime())); jtool.setString(pst, r++, e.getRe()); //WHERE //TODO ADD UPDATE SQL WHERE CODE... } /** process 1 row */ private MyTable processRow(ResultSet rs) throws SQLException { int r = 1; MyTable e = null; while (rs.next()) { r = 1; e = new MyTable(); e.setMyid(jtool.getLong(rs, r++)); e.setMytype(jtool.getInt(rs, r++)); e.setMyname(jtool.getStrTrim(rs, r++)); e.setMydate(jtool.getTimestamp(rs, r++)); e.setRe(jtool.getStrTrim(rs, r++)); break; } return e; } /** process n row */ private void processRow(List<MyTable> list, ResultSet rs) throws SQLException { int r = 1; MyTable e = null; while (rs.next()) { r = 1; e = new MyTable(); e.setMyid(jtool.getLong(rs, r++)); e.setMytype(jtool.getInt(rs, r++)); e.setMyname(jtool.getStrTrim(rs, r++)); e.setMydate(jtool.getTimestamp(rs, r++)); e.setRe(jtool.getStrTrim(rs, r++)); list.add(e); } } /** 5 Column */ private String getColumnString() { String sqlSql =" MYID,"+ "MYTYPE,"+ "MYNAME,"+ "MYDATE,"+ "REMARK "; return sqlSql; }}MyTableDaoJdbcImpl 实现代码结束, 注释中有部分条件要修改一下,因为每个表的主键不同,删除条件也不一样. new 一个 Date,用 eclipse 输出,怎么有错误呢 jxl 怎么通过流解析excel文档 如何检查一个Axis上面的服务是否正常啊! [新手问题]关于main方法中的String[] args 编写类似ping命令的 敢问:现在在上海搞电脑(指软件技术方面)哪一行/业最赚钱? SCJP有谁拿了吗?? 将C语言改写成JAVA遇到的问题 如何,配置JAVA环境? 请问java用什么工具编写?在哪里运行?我刚开始学,不太懂,谢谢了 求NetBeans 窗体调用代码 利用对象作为参数,得到阶乘的值
1.传统的原始开发模式
2.MVC开发模式:
SSH(Spring+stauts+hibernate)/SSI(Spring+stauts+ibatis)二种主流集成框架方式: 楼主所说的通过工具生成实体bean,就是hibernate框架做的事,每张表映射生成一个实体bean,项目层次:
分action层->service层->dao层 action是: 响应前台事件,取得前台封装过来的数据; service层:业务逻辑层; dao层:写SQL或HQL,调用数据连接类的CRUD方法和数据库交互;PS:我写了个大概,发现要写的东西实在太多了····楼主可以通过我写的关键字去找资料了解下,比我说来的好
直接用springMVC+springJDBC,
用spring的jdbc就可以实现实体类的绑定,
至于实体类,楼主已经有工具生成,那就省事了,
直接用spring的JDBC即可,举一个crud的例子:
就比如实体类 Bean,,
查询(根据Id查询):public Bean getBean(Integer id) {
StringBuilder selectBuilder = new StringBuilder();
selectBuilder.append(" SELECT * from xxx where id=?");
return new SimpleJdbcTemplate().getSimpleJdbcTemplate().queryForObject(selectBuilder.toString(), new ParameterizedRowMapper<Bean>() { public Bean mapRow(ResultSet rs, int rowNum)
throws SQLException {
Bean bean = new Bean ();
bean.setId(rs.getInt("id"));
bean.xxx(rs.getString("xxx"));
//...设置各个需要存入实体的值
return bean;
}
}, id);
}
楼主,查几十万条数据那也只是交给了数据库去做,我们可以考虑加上分页,每次只找出10条,20条,或者50条等等,但是,如果是涉及到导出,可以另做一个直接用jdbc的方式进行查询,跳过bean绑定。
楼主,查几十万条数据那也只是交给了数据库去做,我们可以考虑加上分页,每次只找出10条,20条,或者50条等等,但是,如果是涉及到导出,可以另做一个直接用jdbc的方式进行查询,跳过bean绑定。
明白了,多谢了
http://blog.csdn.net/xiehuan_uestc/article/details/7969321
http://www.cnblogs.com/Caceolod/articles/1280258.html
http://blog.csdn.net/csh624366188/article/details/7305672
http://www.cnblogs.com/baiweiguo/archive/2013/02/21/2920638.html其实这样的连接还有很多,而且总结的都挺全面的,我只是来蹭分的
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;import javax.sql.DataSource;import org.springframework.jdbc.datasource.DataSourceUtils;import com.wasion.framework.service.ServiceLocator;/**
* 数据库表转换成javaBean对象小工具,
* bean属性按原始数据库字段经过去掉下划线,并大写处理首字母等等.
*
*/
public class MyEntityUtils {
private String tablename = "";
private String[] colnames;
private String[] colTypes;
private int[] colSizes; // 列名大小
private int[] colScale; // 列名小数精度
private boolean importUtil = false; // 导入包java.util.*
private boolean importSql = false; // 包java.sql.*
private boolean importMath = false; // 包java.sql.*
private boolean useObjectType = false; // bean类中使用基本类型还是对象类型 /**
* @param args
*/
public void tableToEntity(String tName) {
tablename = tName;
//数据连Connection获取,自己想办法就行.
DataSource ds = (DataSource) ServiceLocator.getService("dataSource");
Connection conn = DataSourceUtils.getConnection(ds); // 得到数据库连接
String strsql = "SELECT * FROM " + tablename;//+" WHERE ROWNUM=1";
try {
System.out.println(strsql);
PreparedStatement pstmt = conn.prepareStatement(strsql);
pstmt.executeQuery();
ResultSetMetaData rsmd = pstmt.getMetaData();
int size = rsmd.getColumnCount(); // 共有多少列
colnames = new String[size];
colTypes = new String[size];
colSizes = new int[size];
colScale = new int[size];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
rsmd.getCatalogName(i + 1);
colnames[i] = rsmd.getColumnName(i + 1).toLowerCase();
colTypes[i] = rsmd.getColumnTypeName(i + 1).toLowerCase();
colScale[i] = rsmd.getScale(i + 1);
System.out.println(rsmd.getCatalogName(i+1));
if ("datetime".equals(colTypes[i])) {
importUtil = true;
}
if ("image".equals(colTypes[i]) || "text".equals(colTypes[i])) {
importSql = true;
}
if(colScale[i]>0){
importMath = true;
}
colSizes[i] = rsmd.getPrecision(i + 1);
}
String content = parse(colnames, colTypes, colSizes);
try {
FileWriter fw = new FileWriter(initcap(tablename) + ".java");
PrintWriter pw = new PrintWriter(fw);
pw.println(content);
pw.flush();
pw.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DataSourceUtils.releaseConnection(conn, ds);
}
}
/**
* 解析处理(生成实体类主体代码)
*/
private String parse(String[] colNames, String[] colTypes, int[] colSizes) {
StringBuffer sb = new StringBuffer();
sb.append("\r\nimport java.io.Serializable;\r\n");
if (importUtil) {
sb.append("import java.util.Date;\r\n");
}
if (importSql) {
sb.append("import java.sql.*;\r\n\r\n");
}
if(importMath){
sb.append("import java.math.*;\r\n\r\n");
}
//表注释
processColnames(sb);
sb.append("public class " + initcap(tablename) + " implements Serializable {\r\n");
processAllAttrs(sb);
processAllMethod(sb);
sb.append("}\r\n");
System.out.println(sb.toString());
return sb.toString(); }
/**
* 处理列名,把空格下划线'_'去掉,同时把下划线后的首字母大写
* 要是整个列在3个字符及以内,则去掉'_'后,不把"_"后首字母大写.
* 同时把数据库列名,列类型写到注释中以便查看,
* @param sb
*/
private void processColnames(StringBuffer sb) {
sb.append("\r\n/** " + tablename + "\r\n");
String colsiz="";
String colsca="";
for (int i = 0; i < colnames.length; i++) {
colsiz = colSizes[i]<=0? "" : (colScale[i]<=0? "("+colSizes[i]+")" : "("+colSizes[i]+","+colScale[i]+")");
sb.append("\t" + colnames[i].toUpperCase() +" "+colTypes[i].toUpperCase()+ colsiz+"\r\n");
char[] ch = colnames[i].toCharArray();
char c ='a';
if(ch.length>3){
for(int j=0;j <ch.length; j++){
c = ch[j];
if(c == '_'){
if (ch[j+1]>= 'a' && ch[j+1] <= 'z') {
ch[j+1]=(char) (ch[j+1]-32);
}
}
}
}
String str = new String(ch);
colnames[i] = str.replaceAll("_", "");
}
sb.append("*/\r\n");
}
/**
* 生成所有的方法
*
* @param sb
*/
private void processAllMethod(StringBuffer sb) {
for (int i = 0; i < colnames.length; i++) {
sb.append("\tpublic void set" + initcap(colnames[i]) + "("
+ oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " " + colnames[i]
+ "){\r\n");
sb.append("\t\tthis." + colnames[i] + "=" + colnames[i] + ";\r\n");
sb.append("\t}\r\n"); sb.append("\tpublic " + oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " get"
+ initcap(colnames[i]) + "(){\r\n");
sb.append("\t\treturn " + colnames[i] + ";\r\n");
sb.append("\t}\r\n");
}
} /**
* 解析输出属性
*
* @return
*/
private void processAllAttrs(StringBuffer sb) {
sb.append("\tprivate static final long serialVersionUID = 1L;\r\n");
for (int i = 0; i < colnames.length; i++) {
sb.append("\tprivate " + oracleSqlType2JavaType(colTypes[i],colScale[i],colSizes[i]) + " "
+ colnames[i] + ";\r\n");
}
sb.append("\r\n");
} /**
* 把输入字符串的首字母改成大写
* @param str
* @return
*/
private String initcap(String str) {
char[] ch = str.toCharArray();
if (ch[0] >= 'a' && ch[0] <= 'z') {
ch[0] = (char) (ch[0] - 32);
}
return new String(ch);
} /**
* Oracle
* @param sqlType
* @param scale
* @return
*/
private String oracleSqlType2JavaType(String sqlType, int scale,int size) {
if (sqlType.equals("integer")) {
return "Integer";
} else if (sqlType.equals("long")) {
return "Long";
} else if (sqlType.equals("float")) {
return "BigDecimal";
} else if (sqlType.equals("double precision")) {
return "BigDecimal";
} else if (sqlType.equals("number")
||sqlType.equals("decimal")
|| sqlType.equals("numeric")
|| sqlType.equals("real")) {
return scale==0? (size<10? "Integer" : "Long") : "BigDecimal";
}else if (sqlType.equals("varchar")
|| sqlType.equals("varchar2")
|| sqlType.equals("char")
|| sqlType.equals("nvarchar")
|| sqlType.equals("nchar")) {
return "String";
} else if (sqlType.equals("datetime")
|| sqlType.equals("date")
|| sqlType.equals("timestamp")) {
return "Date";
}
return null;
} /**
* @param args
*/
public static void main(String[] args) {
MyEntityUtils t = new MyEntityUtils();
t.tableToEntity("TABLE_NAME");
}}
生成 bean
生成 service接口,service实现
生成 dao接口,dao实现自动实现 1增加,2删除,3修改,4按主键查询,5按给定的where条件查询,
6批理增加,7批删除,8批修改, 9批修改或批增加.
9大方法,
不过我只能提示楼主,多写代码,程序员就是写代码的,这些要自己去做.
天拉, insert, select, update 那个眼都看花!! 这个做个工具就行了代码全部自动生成,
这都是做一次永远使用的代码, 程序员重要是写业务逻辑代码,数据库的代码都应当不要写,或者尽量少写.
哦,这样啊,请问有什么现成的工具么?我学习学习。我以前是用PB的,从来没有写过sql语句,用了java,还以为全要自己写
----建一个表
----===============================
CREATE TABLE MY_TABLE (
MYID NUMBER(10) NOT NULL,
MYTYPE NUMBER(6) NOT NULL,
MYNAME VARCHAR2(32),
MYDATE DATE,
REMARK VARCHAR2(64) ,
CONSTRAINT MY_TABLE PRIMARY KEY (MYID)
);
多谢。bean我已经能够生成,请问能不能给个例子,我学学doa如何生成?谢谢。
并且这是成熟的代码, 对了生成的模板都是 spring (集成spring很简单,网上很多),连接池用 apache的 dbcp包, 不要用什么 hibernate等其它 orm工具 这种东西我在多年的工作中显示他们不适合大型项目,
我的项目中表都是上 10亿-100亿行数据, 我想你的项目也可以.生成样本第1段
import java.io.Serializable;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;
import java.math.*;import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;//此工具自己做,或者自己手工改jdbc原始方法
import com.my.util.JdbcMyUtils;
/**
* MY_TABLE
* @author table tool 2013-12-30
*/
public class MyTableDaoJdbcImpl extends JdbcDaoSupport implements IMyTableDao {
private Log logger = LogFactory.getLog(this.getClass());
private JdbcMyUtils jtool = new JdbcMyUtils();
private String table = "MY_TABLE";
private String name = "MY_TABLE";
private final int BATCH_SIZE=500; /** findAll row */
public List<MyTable> findAll(String sqlExpression) {
if(sqlExpression==null) return null;
String tablename = null;
String strSql = null;
List<MyTable> list = new ArrayList<MyTable>();
tablename = table;
strSql =" SELECT "+this.getColumnString()+" FROM "+ tablename+" "+sqlExpression;
//call Template
this.findEntityTemplate(list, strSql);
return list;
} /** findAll row */
public List<MyTable> findAll() {
String tablename = null;
String strSql = null;
List<MyTable> list = new ArrayList<MyTable>();
tablename = table;
strSql =" SELECT "+this.getColumnString()+" FROM "+ tablename;
//call Template
this.findEntityTemplate(list, strSql);
return list;
} /** isExist row */
public boolean isExist(Long arg0){
if(arg0==null) return false;
String tablename = null;
String strSql = null;
boolean boolReturn = false;
tablename = table;// + MonTableUtils.monthTable(dataMonth, table);
strSql =" SELECT COUNT(0) FROM "+ tablename+" WHERE ID="+arg0;
//TODO ADD WHERE CODE......
//call Template
int rows = this.updateSqlTemplate(strSql);
if(rows>0) boolReturn = true;
return boolReturn;
} /** read row */
public MyTable read(Long arg0) {
if(arg0==null) return null;
String tablename = null;
String strSql = null;
MyTable e = null;
tablename = table;
strSql = "SELECT "+this.getColumnString()+" FROM "+tablename+" WHERE ID="+arg0;
//TODO ADD WHERE CODE......
//call Template
e = findEntityTemplate(strSql);
return e;
} /** create row */
public Long create(MyTable entity) {
if(entity==null) return null;
String tablename = null;
String strSql = null;
boolean boolReturn = false;
Long id=0L; //entity id;
tablename = table;
strSql =" INSERT INTO "+ tablename +" ("+this.getColumnString()+") "+
" VALUES(?,?,?,?,?)";
//call Template
boolReturn = insertEntityTemplate(entity, strSql);
return boolReturn ? id : null;
} /** delete row */
public boolean delete(MyTable entity) {
if(entity==null) return false;
String tablename = null;
String strSql = null;
boolean boolReturn = false;
tablename = table;
strSql =" DELETE "+ tablename +
" WHERE ID=1..... ";
//TODO ADD WHERE CODE......
//call Template
boolReturn = deleteEntityTemplate(entity, strSql);
return boolReturn;
} /** delete row */
public boolean delete(Long arg0) {
if(arg0==null) return false;
String tablename = null;
String strSql = null;
boolean boolReturn = false;
tablename = table;
strSql =" DELETE "+ tablename +
" WHERE ID=1..... ";
//TODO ADD WHERE CODE......
//call Template
int rows = this.updateSqlTemplate(strSql);
if(rows>=0) boolReturn = true;
return boolReturn;
} /** update row */
public boolean update(MyTable entity) {
if(entity==null) return false;
String tablename = null;
String strSql = null;
boolean boolReturn = false;
tablename = table;
strSql =" UPDATE "+ tablename +" SET"+
" MYID=?,MYTYPE=?,MYNAME=?,MYDATE=?,REMARK=?"+
" WHERE ID=1..... ";
//TODO ADD WHERE CODE......
//call Template
boolReturn = updateEntityTemplate(entity, strSql);
return boolReturn;
}
//-------------------------Template CODE-------------------------
/** insert,delete,update Sql Template
* UPDATE,DELETE RETURN >=0 SUCCEED, INSERT >0 SUCCEED;
* JDBC:0-NO DATA, 1-SUCCEED, -1-FAIL.
*/
private int updateSqlTemplate(String strSql){
if(strSql==null) return 0;
int rows=0;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
rows = this.getJdbcTemplate().update(strSql);
} catch (Exception ex) {
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
}
return rows;
} /** insert,delete,update Sql Template
* UPDATE,DELETE RETURN >=0 SUCCEED, INSERT >0 SUCCEED;
* JDBC:0-NO DATA, 1-SUCCEED, -1-FAIL.
*/
private int updateSqlTemplate(String strSql, Object[] args){
if(strSql==null) return 0;
int rows=0;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
rows = this.getJdbcTemplate().update(strSql, args);
} catch (Exception ex) {
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
}
return rows;
} /** insertTemplate */
private boolean insertEntityTemplate(MyTable entity, String strSql){
if(entity==null) return false;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
int rows=0;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
pst = con.prepareStatement(strSql);
this.processInsertRow(pst, entity);
rows = pst.executeUpdate();
// INSERT SQL RETURN >0 SUCCEED
if(rows>0) boolReturn = true;
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
JdbcUtils.closeStatement(pst);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** deleteTemplate */
private boolean deleteEntityTemplate(MyTable entity, String strSql){
if(entity==null) return false;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
int rows=0;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
pst = con.prepareStatement(strSql);
this.processDeleteRow(pst, entity);
rows = pst.executeUpdate();
// DELETE SQL RETURN >=0 SUCCEED (=0 NO DATA)
if(rows>=0) boolReturn = true;
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
JdbcUtils.closeStatement(pst);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** updataTemplate */
private boolean updateEntityTemplate(MyTable entity, String strSql){
if(entity==null) return false;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
int rows=0;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
pst = con.prepareStatement(strSql);
this.processUpdateRow(pst, entity);
rows = pst.executeUpdate();
// UPDATE SQL RETURN >=0 SUCCEED (=0 NO DATA)
if(rows>=0) boolReturn = true;
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
JdbcUtils.closeStatement(pst);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** findEntityTemplate one entity */
private MyTable findEntityTemplate(String strSql){
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
MyTable e=null;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
pst = con.prepareStatement(strSql);
rs = pst.executeQuery();
e =this.processRow(rs);
} catch (Exception ex) {
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
JdbcUtils.closeStatement(pst);
JdbcUtils.closeResultSet(rs);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return e;
} /** findEntityTemplate list entity */
private void findEntityTemplate(List<MyTable> listout, String strSql){
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
pst = con.prepareStatement(strSql);
rs = pst.executeQuery();
this.processRow(listout, rs);
} catch (Exception ex) {
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
JdbcUtils.closeStatement(pst);
JdbcUtils.closeResultSet(rs);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
} /** insertBatchTemplate */
private boolean insertBatchTemplate(List<MyTable> entities, String strSql){
if(entities==null || entities.size()==0) return true;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
MyTable entity =null;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
con.setAutoCommit(false);
pst = con.prepareStatement(strSql);
int i=0;
for(int j=0; j<entities.size(); j++){
entity = entities.get(j);
this.processInsertRow(pst, entity);
pst.addBatch();
i++;
if(i >= BATCH_SIZE){
if(logger.isDebugEnabled()){
logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name);
}
i=0;
pst.executeBatch();
pst.clearBatch();
con.commit();
}
}
//最后把不足行的数据提交
pst.executeBatch();
pst.clearBatch();
boolReturn = true;
if(logger.isDebugEnabled()){
logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name);
}
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
entity = null;
JdbcUtils.closeStatement(pst);
jtool.commit(con, boolReturn);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** deleteBatchTemplate */
private boolean deleteBatchTemplate(List<MyTable> entities, String strSql){
if(entities==null || entities.size()==0) return true;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
MyTable entity =null;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
con.setAutoCommit(false);
pst = con.prepareStatement(strSql);
int i=0;
for(int j=0; j<entities.size(); j++){
entity = entities.get(j);
this.processDeleteRow(pst, entity);
pst.addBatch();
i++;
if(i >= BATCH_SIZE){
if(logger.isDebugEnabled()){
logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name);
}
i=0;
pst.executeBatch();
pst.clearBatch();
con.commit();
}
}
//最后把不足行的数据提交
pst.executeBatch();
pst.clearBatch();
boolReturn = true;
if(logger.isDebugEnabled()){
logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name);
}
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
entity = null;
JdbcUtils.closeStatement(pst);
jtool.commit(con, boolReturn);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
}
/** updateBatchTemplate */
private boolean updateBatchTemplate(List<MyTable> entities, String strSql){
if(entities==null || entities.size()==0) return true;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pst = null;
MyTable entity =null;
try {
if(logger.isDebugEnabled())
logger.debug(name+": "+strSql);
con = this.getConnection();
con.setAutoCommit(false);
pst = con.prepareStatement(strSql);
int i=0;
for(int j=0; j<entities.size(); j++){
entity = entities.get(j);
this.processUpdateRow(pst, entity);
pst.addBatch();
i++;
if(i >= BATCH_SIZE){
if(logger.isDebugEnabled()){
logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name);
}
i=0;
pst.executeBatch();
pst.clearBatch();
con.commit();
}
}
//最后把不足行的数据提交
pst.executeBatch();
pst.clearBatch();
boolReturn = true;
if(logger.isDebugEnabled()){
logger.debug("[完成批量发送数据]: "+i+" 行,BY "+name);
}
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+strSql, ex);
} finally {
strSql = null;
entity = null;
JdbcUtils.closeStatement(pst);
jtool.commit(con, boolReturn);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** updateOrInsertBatchTemplate */
private boolean updateOrInsertBatchTemplate(List<MyTable> entities, String strUpSql, String strInSql){
if(entities==null || entities.size()==0) return true;
boolean boolReturn = false;
Connection con = null;
PreparedStatement pstup = null;
PreparedStatement pstin = null;
MyTable entity =null;
List<MyTable> failedData =new ArrayList<MyTable>();
int isuporin =0;
int failedRow =0;
int insertRow =0;
int updateRow =0;
int forRow =0;
try {
//update
if(logger.isDebugEnabled())
logger.debug(name+": "+strUpSql);
con = this.getConnection();
con.setAutoCommit(false);
pstup = con.prepareStatement(strUpSql);
int i=0;
forRow = entities.size();
for(int j=0; j<forRow; j++){
entity = entities.get(j);
this.processUpdateRow(pstup, entity);
pstup.addBatch();
i++;
if(i >= BATCH_SIZE || (j+1)==forRow){
if(logger.isDebugEnabled()){
logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name);
}
i=0;
int[] ri =pstup.executeBatch();
pstup.clearBatch();
con.commit();
//记录失败的数据.
for(int x=0;x<ri.length; x++){
if(ri[x]==Statement.EXECUTE_FAILED || ri[x]<-3){
failedData.add(entities.get(x));
}
}
}
}
//insert
if(failedData.size()>0){
isuporin =1;
if(logger.isDebugEnabled())
logger.debug(name+": "+strInSql);
pstin = con.prepareStatement(strInSql);
i=0;
forRow = failedData.size();
for(int j=0; j<forRow; j++){
entity = failedData.get(j);
this.processInsertRow(pstin, entity);
pstin.addBatch();
i++;
if(i >= BATCH_SIZE || (j+1)==forRow){
if(logger.isDebugEnabled()){
logger.debug("[正在批量发送数据]: "+i+" 行,BY "+name);
}
i=0;
int[] ri =pstin.executeBatch();
pstin.clearBatch();
con.commit();
//记录失败的数据.
for(int x=0;x<ri.length; x++){
if(ri[x]==Statement.EXECUTE_FAILED || ri[x]<-3){
failedRow = failedRow + 1;
}
}
}
insertRow = failedData.size() - failedRow;
}
}
updateRow = entities.size() -failedData.size();
boolReturn = true;
} catch (Exception ex) {
boolReturn = false;
logger.error("[execute sql error]: "+(isuporin==0? strUpSql : strInSql), ex);
} finally {
if(logger.isDebugEnabled())
logger.info("[完成批量发送数据]: 总行"+entities.size()+", 更新"+updateRow+", 插入"+insertRow+", 失败"+failedRow+", BY "+name);
entity = null;
failedData.clear();
failedData =null;
JdbcUtils.closeStatement(pstup);
JdbcUtils.closeStatement(pstin);
jtool.commit(con, boolReturn);
DataSourceUtils.releaseConnection(con, this.getDataSource());
}
return boolReturn;
} /** Insert row */
private void processInsertRow(PreparedStatement pst, MyTable e) throws SQLException {
int r = 1;
jtool.setLong(pst, r++, e.getMyid());
jtool.setInt(pst, r++, e.getMytype());
jtool.setString(pst, r++, e.getMyname());
jtool.setTimestamp(pst, r++, new Timestamp(e.getMydate().getTime()));
jtool.setString(pst, r++, e.getRe());
} /** Delete row */
private void processDeleteRow(PreparedStatement pst, MyTable e) throws SQLException {
int r = 1;
//DEMO CODE: jtool.setLong(pst, r++, e.getId());
//TODO ADD DELETE SQL WHERE CODE...
} /** Update row */
private void processUpdateRow(PreparedStatement pst, MyTable e) throws SQLException {
int r = 1;
jtool.setLong(pst, r++, e.getMyid());
jtool.setInt(pst, r++, e.getMytype());
jtool.setString(pst, r++, e.getMyname());
jtool.setTimestamp(pst, r++, new Timestamp(e.getMydate().getTime()));
jtool.setString(pst, r++, e.getRe());
//WHERE
//TODO ADD UPDATE SQL WHERE CODE...
} /** process 1 row */
private MyTable processRow(ResultSet rs) throws SQLException {
int r = 1;
MyTable e = null;
while (rs.next()) {
r = 1;
e = new MyTable();
e.setMyid(jtool.getLong(rs, r++));
e.setMytype(jtool.getInt(rs, r++));
e.setMyname(jtool.getStrTrim(rs, r++));
e.setMydate(jtool.getTimestamp(rs, r++));
e.setRe(jtool.getStrTrim(rs, r++));
break;
}
return e;
} /** process n row */
private void processRow(List<MyTable> list, ResultSet rs) throws SQLException {
int r = 1;
MyTable e = null;
while (rs.next()) {
r = 1;
e = new MyTable();
e.setMyid(jtool.getLong(rs, r++));
e.setMytype(jtool.getInt(rs, r++));
e.setMyname(jtool.getStrTrim(rs, r++));
e.setMydate(jtool.getTimestamp(rs, r++));
e.setRe(jtool.getStrTrim(rs, r++));
list.add(e);
}
} /** 5 Column */
private String getColumnString() {
String sqlSql =" MYID,"+
"MYTYPE,"+
"MYNAME,"+
"MYDATE,"+
"REMARK ";
return sqlSql;
}}
MyTableDaoJdbcImpl 实现代码结束, 注释中有部分条件要修改一下,因为每个表的主键不同,删除条件也不一样.