封装的DBUtil 简化增删改查

来源:互联网 发布:小儿常见病推拿软件 编辑:程序博客网 时间:2024/03/29 15:54


1、数据库配置文件 database.properties

#jdbc propertiesdb.driver=oracle.jdbc.driver.OracleDriverdb.url=jdbc:oracle:thin:@172.16.11.81:1521:orcldb.username=kpchendb.password=kpchen

2、加载数据库配置文件JDBC.java

package com.ilfy.lbjn.utils;import java.io.InputStream;import java.util.Properties;public class JDBC {/** * jdbc Properties */private static Properties pro;/** * db user */private String user;/** * db pass */private String pass;/** * db driver */private String driver;/** * db url */private String url;public JDBC() {loadFile();this.url = pro.getProperty("db.url");this.pass = pro.getProperty("db.password");this.driver = pro.getProperty("db.driver");this.user = pro.getProperty("db.username");}/** * 加载数据库配置文件 如果没有配置文件将使用默认值 */private void loadFile() {if (pro == null) {try {pro = new Properties();Class<?> cls = JDBC.class;ClassLoader cl = cls.getClassLoader();InputStream in = cl.getResourceAsStream("database.properties");pro.load(in);} catch (Exception e) {e.printStackTrace();pro.put("db.driver", "org.gjt.mm.mysql.Driver");pro.put("db.url", "jdbc:mysql://201.45.67.165:3306/login");pro.put("db.username", "root");pro.put("db.password", "root");}}}public String getUser() {return user;}public String getPass() {return pass;}public String getDriver() {return driver;}public String getUrl() {return url;}public String getProV(String key) {return pro.getProperty(key);}}

3、封装增删改查 DBUtil.java

package com.ilfy.lbjn.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.sun.rowset.CachedRowSetImpl;public class DBUtil {/** * 数据库配置信息 */private static JDBC jdbc = new JDBC();/** * 数据库连接 */private Connection conn;public DBUtil() {conn = getConnection();}// 注册驱动static {try {Class.forName(jdbc.getDriver());} catch (ClassNotFoundException e) {e.printStackTrace();}}/** * 插入数据 *  * @param sql * @param params * @return */public int insert(String sql, Object... params) {return executeUpdate(sql, params);}/** * 更新数据 *  * @param sql * @param params * @return */public int update(String sql, Object... params) {return executeUpdate(sql, params);}/** * 删除数据 *  * @param sql * @param params * @return */public int delete(String sql, Object... params) {return executeUpdate(sql, params);}/** * 执行DDL DML *  * @param conn * @param sql * @return */public int executeUpdate(String sql, Object... params) {int rlt = 0;try {PreparedStatement pst = null;pst = conn.prepareStatement(sql);putParams(pst, params);rlt = pst.executeUpdate();pst.close();} catch (SQLException e) {e.printStackTrace();}return rlt;}/** * 执行查询语句 *  * @param conn * @param sql * @return */public CachedRowSetImpl query(String sql, Object... params) {CachedRowSetImpl rowset = null;PreparedStatement pst = null;try {pst = conn.prepareStatement(sql);putParams(pst, params);ResultSet rs = pst.executeQuery();rowset = new CachedRowSetImpl();rowset.populate(rs);rs.close();pst.close();} catch (SQLException e) {e.printStackTrace();}return rowset;}/** * 获取数据库连接 *  * @return */public Connection getConnection() {Connection conn = null;try {conn = DriverManager.getConnection(jdbc.getUrl(), jdbc.getUser(),jdbc.getPass());} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 往预编译语句中放置参数 *  * @param pst * @param params *            [] * @throws SQLException */private void putParams(PreparedStatement pst, Object... params)throws SQLException {if (params != null) {for (int i = 0; i < params.length; i++) {pst.setObject(i + 1, params[i]);}}}/** * 关闭数据库链接 */public void close() {if (this.conn != null) {try {this.conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

4、使用工具类DBUtil对数据库进行增删改查操作

package com.ilfy.lbjn.dao;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.ilfy.lbjn.pojo.User;import com.ilfy.lbjn.utils.DBUtil;import com.sun.rowset.CachedRowSetImpl;/** * @desc: lbjn * @author: Administrator * @createTime: 2014年11月10日 上午11:11:48 * @history: * @version: v1.0 */public class UserDao {/** * @author: kpchen * @createTime: 2014年11月10日 下午8:22:26 * @history: * @return List<User> */public List<User> findAllUsers() {List<User> userLists = new ArrayList<User>();DBUtil dbUtil = null;User user = null;CachedRowSetImpl rs = null;String sql = "select * from tb_user";try {dbUtil = new DBUtil();rs = dbUtil.query(sql);while (rs.next()) {user = new User(rs.getInt("USERID"), rs.getString("USERNAME"),rs.getString("PASSWORD"), rs.getInt("ROLE"));userLists.add(user);}} catch (Exception e) {e.printStackTrace();} finally {try {rs.close();dbUtil.close();} catch (SQLException e) {e.printStackTrace();}}return userLists;}/** * @author: kpchen * @createTime: 2014年11月10日 下午8:22:20 * @history: * @param id *            void */public void delUser(String id) {DBUtil dbUtil = null;String sql = "delete from tb_user where userId=" + id;try {dbUtil = new DBUtil();dbUtil.delete(sql);} catch (Exception e) {e.printStackTrace();} finally {dbUtil.close();}}/** * @author: kpchen * @createTime: 2014年11月10日 下午8:40:26 * @history: * @param user *            void */public void addUser(User user) {DBUtil dbUtil = null;String sql = "insert into tb_user(userId,userName,password,role)values(?,?,?,?)";Object[] obj = { user.getUserId(), user.getUserName(),user.getPassword(), user.getRole() };try {dbUtil = new DBUtil();dbUtil.insert(sql, obj);} catch (Exception e) {e.printStackTrace();} finally {dbUtil.close();}}/** * @author: kpchen * @createTime: 2014年11月10日 下午8:48:02 * @history: * @param id * @return User */public User getUserById(String id) {DBUtil dbUtil = null;User user = null;CachedRowSetImpl rs = null;String sql = "select * from tb_user where userId=" + id;try {dbUtil = new DBUtil();rs = dbUtil.query(sql);if (rs.next()) {user = new User(rs.getInt("USERID"), rs.getString("USERNAME"),rs.getString("PASSWORD"), rs.getInt("ROLE"));}} catch (Exception e) {e.printStackTrace();} finally {try {rs.close();dbUtil.close();} catch (SQLException e) {e.printStackTrace();}}return user;}/** * @author: kpchen * @createTime: 2014年11月10日 下午8:55:10 * @history: * @param user *            void */public void updateUser(User user) {DBUtil dbUtil = null;String sql = "update tb_user set userName=?, password=? ,role=? where userId = ?";Object[] obj = { user.getUserName(), user.getPassword(),user.getRole(), user.getUserId() };try {dbUtil = new DBUtil();dbUtil.update(sql, obj);} catch (Exception e) {e.printStackTrace();} finally {dbUtil.close();}}/** * @author: kpchen * @createTime: 2014年11月10日 下午9:18:30 * @history: * @param where1 * @return List<User> */public List<User> findUsersByUserName(String where1) {List<User> userLists = new ArrayList<User>();DBUtil dbUtil = null;User user = null;CachedRowSetImpl rs = null;String sql = "select * from tb_user where 1=1 " + where1;try {dbUtil = new DBUtil();rs = dbUtil.query(sql);while (rs.next()) {user = new User(rs.getInt("USERID"), rs.getString("USERNAME"),rs.getString("PASSWORD"), rs.getInt("ROLE"));userLists.add(user);}} catch (Exception e) {e.printStackTrace();} finally {try {rs.close();dbUtil.close();} catch (SQLException e) {e.printStackTrace();}}return userLists;}}

5、源码:http://pan.baidu.com/s/14KoJc

0 0
原创粉丝点击