| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
pbd4
9年前发布

JDBC工具类

1、连接数据库

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-14    */    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-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, pr);        }            /**        * 关闭连接        */        public void disconnect() {            try {                if (con != null) {                    con.close();                }            } catch (SQLException ex) {                ex.printStackTrace();            }        }            /**        * 获取Connection对象        */        public Connection getCon() {            return con;        }    }  

2、操作数据库
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-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) {            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参数(一个二维数组)        * @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;            }        }            /**        * 执行一条插入语句,同时返回插入时的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);        }    }