国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > 数据库应用 > JDBC工具类

JDBC工具类

来源:程序员人生   发布时间:2015-01-22 08:16:27 阅读次数:2798次

1、连接数据库

      

package com.society.demo; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; /** * 连接数据库 * * @author liyulin lyl010991@126.com * @version 1.0 2015-01⑴4 */ public class DBConnection { private Connection con = null; private String user = "root"; private String password = "lyl123"; private String serverIp = "localhost"; private String database = "test"; 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⑻"); 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, 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 lyl010991@126.com * @version 1.0 2015-01⑴4 */ 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); } } /** * 履行1条sql语句(增、删、改) * * @param sql 插入sql语句 * @param params sql语句中?所对应的值 * @return 是不是插入成功 */ public boolean executeSQL(String sql, Object[] params) { boolean tag = false;// 操作是不是成功标志 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]); } } ps.executeUpdate(); tag = true; } catch (Exception e) { e.printStackTrace(); } finally { closeDB(); return tag; } } /** * 批量操作(增、删、改) * * @param sqls 插入sql语句 * @param objs sql参数(1个2维数组) * @return */ public boolean executeBatch(List<String> sqls, Object[][] objs) { boolean tag = false;// 批量操作是不是成功标志 connectDB(); try { conn.setAutoCommit(false); if (null != objs) { // sql参数为null for (int i = 0, size = sqls.size(); i < size; i++) { String sql = sqls.get(i); ps = conn.prepareStatement(sql); if (null != objs[i]) { for (int j = 0, paramsSize = objs[i].length; j < paramsSize; j++) { ps.setObject(j + 1, objs[i][j]); } } ps.executeUpdate(); } } else { for (int i = 0, size = sqls.size(); i < size; i++) { String sql = sqls.get(i); ps = conn.prepareStatement(sql); ps.executeUpdate(); } } conn.commit(); tag = true; } catch (Exception e) { try { conn.rollback(); } catch (SQLException ex) { Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex); } e.printStackTrace(); } finally { closeDB(); return tag; } } /** * 履行1条插入语句,同时返回插入时的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(); boolean tag = db.executeBatch(sqls, objs); System.out.println("tag===>" + tag); } }


生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生