本文介绍了一个SQL实用工具类的设计实现，包括插入、查询、更新等数据库操作的方法，并提供了批量插入数据的示例代码。
public class SqlUtils { private static String sql=""; private static ResultSet res; static Gson gson =new Gson(); static MySQLPool pool = MySQLPool.getInstance(); static Connection conn = pool.getConnection(); private static Statement stmt ; static String json=""; /* public static void insertBigData(SpringBatchHandler sbh) { Long begin = new Date().getTime(); JdbcTemplate jdbcTemplate = sbh.getJdbcTemplate(); final int count = 10000; String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)"; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { // 为prepared statement设置参数。这个方法将在整个过程中被调用的次数 public void setValues(PreparedStatement pst, int i) throws SQLException { pst.setLong(1, i); pst.setInt(2, i); } // 返回更新的结果集条数 public int getBatchSize() { return count; } }); Long end = new Date().getTime(); System.out.println("cast : " + (end - begin) / 1000 + " ms"); } */ public static Boolean insert(String sql){ System.out.println(sql); Boolean b=true; try { stmt = conn.createStatement(); // System.out.println("-hsql-" +hsql); b = stmt.execute(sql); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return false; }finally{ pool.releaseConnection(conn, stmt, res); } return b; } public static void insert() { System.out.println(sql); Boolean b=true; // 开时时间 Long begin = new Date().getTime(); // sql前缀 String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES "; try { // 保存sql后缀 StringBuffer suffix = new StringBuffer(); // 设置事务为非自动提交 conn.setAutoCommit(false); // Statement st = conn.createStatement(); // 比起st，pst会更好些 PreparedStatement pst = conn.prepareStatement(""); // 外层循环，总提交事务次数 for (int i = 1; i <= 100; i++) { // 第次提交步长 for (int j = 1; j <= 10000; j++) { // 构建sql后缀 suffix.append("(" + j * i + ", SYSDATE(), " + i * j * Math.random() + "),"); } // 构建完整sql String sql = prefix + suffix.substring(0, suffix.length() - 1); // 添加执行sql pst.addBatch(sql); // 执行操作 pst.executeBatch(); // 提交事务 conn.commit(); // 清空上一次添加的数据 suffix = new StringBuffer(); } // 头等连接 pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } // 结束时间 Long end = new Date().getTime(); // 耗时 System.out.println("cast : " + (end - begin) / 1000 + " ms"); } public static int dropTable(String tableName){ try { stmt = conn.createStatement(); String sql = "drop table " + tableName; stmt.executeQuery(sql); return SysStatus.QJJ_SUCCESSED; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return SysStatus.QJJ_FAILED; }finally{ pool.releaseConnection(conn, stmt, res); } } public static int createTable(String hsql){ try { stmt = conn.createStatement(); System.out.println(hsql); stmt.execute(hsql); return SysStatus.QJJ_SUCCESSED; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return SysStatus.QJJ_FAILED; }finally{ pool.releaseConnection(conn, stmt, res); } } public static String select(String sql){ String tmp=""; try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); while (res.next()) { System.out.println(res.getDate(1)); tmp=res.getDate(1)+""; } System.out.println("执行运行结�?::" +sql); return ""; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ return tmp; } } public static int update(String sql){ System.out.println("--" +sql); int numb = 0; try { stmt = conn.createStatement(); numb = stmt.executeUpdate(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return numb; } public static long updateLarge(String sql){ System.out.println("--" +sql); long numb = 0; try { stmt = conn.createStatement(); int[] as = null; numb = stmt.executeLargeUpdate("", as); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return numb; } public static ResultSet result(String sql){ System.out.println("--" +sql); try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return res; } public static String selectTime(String sql){ String tmp=""; try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); while (res.next()) { System.out.println(res.getTimestamp(1)); tmp=res.getTimestamp(1)+""; } System.out.println("执行运行结�?::" +sql); return ""; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ return tmp; } } public static String sql(String sql,String tableName){ try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); ResultSetMetaData md = res.getMetaData(); int columnCount = md.getColumnCount(); json="["; while (res.next()){ Map rowData = new LinkedHashMap(); for (int i = 1; i <= columnCount; i++) { if( (res.getObject(i)+"").contains(".")==true){ rowData.put(md.getColumnName(i), ReplaceUtil.reNum( res.getObject(i)+"")); }else if((res.getObject(i)+"").contains(".")==false){ rowData.put(md.getColumnName(i), res.getObject(i)); } } json+=gson.toJson(rowData).replaceAll(" ", "") +","; } json= (String) json.subSequence(0, json.length()-1); json=json+"]"; json=json.replace("bankid", "BankId"); System.out.println(sql +"执行“select * from”运行结果?::" +json); return json; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ return json; } } public static String sqlJson(String sql,String tableName){ try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); ResultSetMetaData md = res.getMetaData(); int columnCount = md.getColumnCount(); json="["; while (res.next()){ Map<String, Object> rowData = new LinkedHashMap<String, Object>(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), res.getObject(i)); } json+=gson.toJson(rowData).replaceAll(" ", "") +","; } json= (String) json.subSequence(0, json.length()-1); json=json+"]"; System.out.println(json+ "执行“select * from”运行结�?::" +sql); return json; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ return json; } } @SuppressWarnings("finally") public static String gson(String sql){ System.out.println(sql); try { stmt = conn.createStatement(); res = stmt.executeQuery(sql); ResultSetMetaData md = res.getMetaData(); int columnCount = md.getColumnCount(); json="["; while (res.next()){ Map rowData = new LinkedHashMap(); for (int i =columnCount; i >0; i--) { rowData.put(md.getColumnName(i), res.getObject(i)); } json+=gson.toJson(rowData).replaceAll(" ", "") +","; } json= (String) json.subSequence(0, json.length()-1); json=json+"]"; // System.out.println("执行“select * from”运行结�?::" +sql); return json; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ pool.releaseConnection(conn, stmt, res); return json; } } public static ResultSet describeTables(String tableName){ try { stmt = conn.createStatement(); String sql = "describe " + tableName; res = stmt.executeQuery(sql); System.out.println("执行“describe Tables”运行结�?::"); if (res.next()) { System.out.println(res.getString(1)); } return res; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ pool.releaseConnection(conn, stmt, res); } return null; } @SuppressWarnings("finally") public static int loadDataIntoTable(String tableName,String filepath){ try { stmt = conn.createStatement(); String sql = "load data local inpath '" + filepath + "' into table " + tableName; res = stmt.executeQuery(sql); return SysStatus.QJJ_SUCCESSED; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return SysStatus.QJJ_FAILED; }finally{ pool.releaseConnection(conn, stmt, res); return SysStatus.QJJ_SUCCESSED; } } public static ResultSet query(String hsql){ try { stmt = conn.createStatement(); res = stmt.executeQuery(hsql); if (res != null) { return res; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ pool.releaseConnection(conn, stmt, res); } return res; } public static String sum(String sql){ try { if(sql.contains("From")==false){ System.out.println("必须使用 大写的 From"); }else{ sql=sql.substring(sql.lastIndexOf("From"), sql.length()); sql=" SELECT count(1) " +sql; System.out.println(sql); json=SqlUtils.gson(sql); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return json; } import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.Map; import java.util.Map.Entry; import java.util.concurrent.ConcurrentHashMap; import com.fz.util.Global; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class MySQLPool { private static volatile MySQLPool pool; private MysqlDataSource ds; private Map<Connection, Boolean> map; private static String url = Global.getConfig("jdbc.url"); private static String username = Global.getConfig("jdbc.username"); private static String password = Global.getConfig("jdbc.password"); private int initPoolSize = 100; private int maxPoolSize = 1000; private int waitTime = 10000; private MySQLPool() { init(); } public static MySQLPool getInstance() { if (pool == null) { synchronized (MySQLPool.class) { if(pool == null) { pool = new MySQLPool(); } } } return pool; } private void init() { try { ds = new MysqlDataSource(); ds.setUrl(url); ds.setUser(username); ds.setPassword(password); ds.setCacheCallableStmts(true); ds.setConnectTimeout(1000); ds.setLoginTimeout(2000); ds.setUseUnicode(true); ds.setEncoding("UTF-8"); ds.setZeroDateTimeBehavior("convertToNull"); ds.setMaxReconnects(5); ds.setAutoReconnect(true); map = new ConcurrentHashMap<Connection, Boolean>(initPoolSize); for (int i = 0; i < initPoolSize; i++) { map.put(getNewConnection(), true); } } catch (Exception e) { e.printStackTrace(); } } public Connection getNewConnection() { try { return ds.getConnection(); } catch (Exception e) { e.printStackTrace(); } return null; } public synchronized Connection getConnection() { Connection conn = null; try { for (Entry<Connection, Boolean> entry : map.entrySet()) { if (entry.getValue()) { conn = entry.getKey(); map.put(conn, false); break; } } if (conn == null) { if (map.size() < maxPoolSize) { conn = getNewConnection(); map.put(conn, false); } else { wait(waitTime); conn = getConnection(); } } } catch (Exception e) { e.printStackTrace(); } return conn; } /* public synchronized Statement createSta(Statement stmt) throws Exception { Connection conn = pool.getConnection(); try { if (stmt == null) { stmt=conn.createStatement(); } } catch (Exception e) { e.printStackTrace(); } return stmt; }*/ public void releaseConnection(Connection conn ,Statement stmt ,ResultSet rs) { if (conn == null) { return; } try { if(rs!=null){ try { rs.close(); } catch (Exception e2) { // TODO: handle exception e2.printStackTrace(); } rs=null; } if(stmt!=null){ try { stmt.close(); } catch (Exception e2) { // TODO: handle exception e2.printStackTrace(); } stmt=null; } if(map.containsKey(conn)) { if (conn.isClosed()) { map.remove(conn); } else { if(!conn.getAutoCommit()) { conn.setAutoCommit(true); } map.put(conn, true); } } else { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
