JDBC工具类
1、连接数据库
package com.society.demo; import java.sql.*; import java.util.Properties; /** * 连接数据库 * * @author liyulin [email protected] * @version 1.0 2015-01-14 */ public class DBConnection { private Connection con = null; private String user = "root"; private String password = "lyl123"; private String serverIp = "localhost"; private String database = "xheditor_db"; public DBConnection() { } public DBConnection(String database, String serverIp) { this.database = database; this.serverIp = serverIp; } /** * 加载驱动 建立数据库连接 * * @throws ClassNotFoundException * @throws InstantiationException * @throws IllegalAccessException * @throws SQLException */ public void connect() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException { Properties pr = new Properties(); pr.put("characterEncoding", "UTF-8"); pr.put("useUnicode", "TRUE"); pr.put("user", this.user); pr.put("password", this.password); Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection("jdbc:mysql://" + this.serverIp + "/" + this.database + "?characterEncoding=UTF-8", pr); } /** * 关闭连接 */ public void disconnect() { try { if (con != null) { con.close(); } } catch (SQLException ex) { ex.printStackTrace(); } } /** * 获取Connection对象 */ public Connection getCon() { return con; } }
2、操作数据库
package com.society.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; /** * JDBC工具类 * * @author liyulin [email protected] * @version 1.0 2015-01-14 */ public class JDBC { private DBConnection db = null; private Connection conn = null; private PreparedStatement ps = null; private ResultSet rs = null; /** * 建立数据库连接 */ public Connection connectDB() { db = new DBConnection(); try { db.connect(); conn = db.getCon(); } catch (Exception ex) { Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex); } return conn; } /** * 关闭数据库 */ public void closeDB() { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex); } } /** * 执行一条sql语句(增、删、改) * * @param sql 插入sql语句 * @param params sql语句中?所对应的值 * @return 是否插入成功 */ public boolean executeSQL(String sql, Object[] params) { connectDB(); try { ps = conn.prepareStatement(sql); if (null != params) { for (int i = 0, paramsSize = params.length; i < paramsSize; i++) { ps.setObject(i + 1, params[i]); } } return (ps.executeUpdate() > 0) ? true : false; } catch (Exception e) { e.printStackTrace(); } finally { closeDB(); } return false; } /** * 批量操作(增、删、改) * * @param sqls 插入sql语句 * @param objs sql参数(一个二维数组) * @return */ public boolean executeBatch(List<String> sqls, Object[][] objs) { connectDB(); try { conn.setAutoCommit(false); for (int i = 0, size = sqls.size(); i < size; i++) { String sql = sqls.get(i); ps = conn.prepareStatement(sql); if (null != null && null != objs[i]) { for (int j = 0, paramsSize = objs[i].length; j < paramsSize; j++) { ps.setObject(j + 1, objs[i][j]); } } ps.executeUpdate(); } conn.commit(); } catch (Exception e) { try { conn.rollback(); } catch (SQLException ex) { Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex); } e.printStackTrace(); } finally { closeDB(); } return true; } /** * 执行一条插入语句,同时返回插入时的pk * * @param sql * @param params * @return pk */ public int insertAndGetPk(String sql, Object[] params) { int key = 0; connectDB(); try { conn.setAutoCommit(false); ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (null != params) { for (int i = 0, paramsSize = params.length; i < paramsSize; i++) { ps.setObject(i + 1, params[i]); } } ps.executeUpdate(); ResultSet keys = ps.getGeneratedKeys(); if (keys.next()) { key = keys.getInt(1); } conn.commit(); } catch (Exception exception) { try { conn.rollback(); exception.printStackTrace(); return 0; } catch (SQLException ex) { Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex); } } finally { closeDB(); } return key; } /** * 查询 * * @param sql sql语句(参数用“?”) * @param params 参数值 * @return */ public ResultSet query(String sql, Object[] params) { try { ps = conn.prepareStatement(sql); if (null != params) { for (int i = 0, paramsSize = params.length; i < paramsSize; i++) { ps.setObject(i + 1, params[i]); } } rs = ps.executeQuery(); } catch (Exception ex) { ex.printStackTrace(); } return rs; } public static void main(String[] agrs) { List<String> sqls = new ArrayList<String>(); sqls.add("insert into image(url,title) values('?','?')"); sqls.add("insert into image(url,title) values('2','222'')"); sqls.add("insert into image(url,title) values(?,?)"); sqls.add("insert into image(url,title) values(?,?)"); Object[][] objs = new Object[][]{ {"1", "111"}, null, {"3", "333"}, {"4", "444"} }; JDBC db = new JDBC(); db.executeBatch(sqls, null); } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。