一、DBUtils
1.简介:commons-dbutils是Apache组织提供的一个开源的JDBC工具类库，它是对JDBC的简单封装,学习成本极低,使用dbutils能够简化jdbc代码量，创建连接，封装结果集，释放资源，并且不影响程序的性能。
2.API介绍
org.apache.commons.dbutils.QueryRunner — 核心
org.apache.commons.dbutils.ResultSetHandler — 结果集封装器
org.apache.commons.dbutils.DbUtils — 工具类
3.QueryRunner类
- 该类简化了sql查询，它与resultSetHandler组合在一起使用可以完成大部分的数据库操作
- 默认的构造方法QueryRunner()
- 需要传入一个连接池作为参数的构造：QueryRunner(DataSource ds),这里需要用到连接池
4.返回数据库连接池
public static DataSource geDataSource(){
return ds;
}
QueryRunner qr=new QueryRunner(DataSource ds);
QueryRunner qr=new QueryRunner(JDBCUtils.getDatasource);
- QueryRunner类的常用方法
-
public Object update(String sql,Object…params)(增删改)（适用于多个参数）
@Override public int insert(String username, String password) { int row = 0; try { String sql = "insert into user values(null,?,?)"; //定义一个数组来存放可变参数,多个参数,也可以把参数依次往后罗列出来 Object[] params = { username, password }; row = qr.update(sql, params); } catch (Exception e) { e.printStackTrace(); } return row; } -
public Object update(String sql,Object param)（适用于单个参数）
@Override public int delete(int id) { int row = 0; try { String sql = "delete from user where id=?"; row = qr.update(sql, id); } catch (Exception e) { } return row; } -
public Object query(String sql,ResultSetHandler,Object…params)（查）（适用于多个参数）
-
public Object query(String sql,ResultSetHandler)（查）（适用于单个参数）
6.ResultSetHandler接口的实现类(直接使用框架写好的类)
-
BeanHandler(Class type):将结果集里面的的数据封装到一个对象里面
@Override public User selectOne(int id) { User user = null; try { String sql = "select * from user where id=?"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // qr.query(sql, rsh) //返回的是单个对象 user = qr.query(sql, new BeanHandler<User>(User.class),id); } catch (Exception e) { e.printStackTrace(); } return user; } -
BeanListHandler(Class type):将结果集里面的每一行数据封装到一个对象里面,把多个对象存放到集合里面
@Override public void selectAll() { try { String sql = "select * from user"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // qr.query(sql, rsh) //返回的是封装对象的集合 List<User> list = qr.query(sql, new BeanListHandler<User>(User.class)); for (User u : list) { System.out.println(u.getId() + "-" + u.getUsername() + "-" + u.getPassword()); } } catch (Exception e) { e.printStackTrace(); } } -
ScalarHandler(int columnIndex):通常用来保存一行一列的结果集,通常用于聚合函数的SQL查询
@Override public void select(String xing) { try { String sql = "select count(*) from user where username like ?"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // 这里只能用long类型来接收,不能使用int类型 long count = qr.query(sql, new ScalarHandler<>(), xing + "%"); System.out.println(count); } catch (Exception e) { e.printStackTrace(); } }
7.直接new接口的匿名实现类
User user = queryRunner.query("select * from account where id = ?", new ResultSetHandler< User>(){
@Override
public User handle(ResultSet rs) throws SQLException {
User user = newUser();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
user.setName(name);
user.setMoney(money);
}
return user;
}
}, id);
System.out.println(account.toString());
二、使用dbutils+c3p0连接池完成简单增删改查（用到外界连接池）
1.将DBUtils的jar包加入到项目工程的build path中。
2.导入c3p0连接池jar包
3.配置c3p0-config.xml文件
-
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <named-config name="c3p0"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/student</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">5</property> <property name="maxPoolSize">10</property> </named-config> </c3p0-config> -
DAO接口
package com.qf.edu.dao; import com.qf.edu.entity.User; public interface JdbcDao { /** * 数据库增删改查方法 * @param username * @param password * @return row 插入成功时影响的行数 */ int insert(String username,String password); int delete(int id); int update(int id ,String username); //查询所有人 void selectAll(); //查单个人 User selectOne(int id); //数据库里面带有聚合函数的查询 void select(String xing); } -
DAOImpl
package com.qf.edu.dao.impl; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import com.qf.edu.dao.JdbcDao; import com.qf.edu.entity.User; import com.qf.edu.utils.JDBCUtils; public class JdbcDaoImpl implements JdbcDao { public static QueryRunner qr = new QueryRunner(JDBCUtils.getDatasource()); //增 @Override public int insert(String username, String password) { int row = 0; try { String sql = "insert into user values(null,?,?)"; Object[] params = { username, password }; row = qr.update(sql, params); } catch (Exception e) { e.printStackTrace(); } return row; } //删 @Override public int delete(int id) { int row = 0; try { String sql = "delete from user where id=?"; row = qr.update(sql, id); } catch (Exception e) { } return row; } //改 @Override public int update(int id,String username) { int row = 0; try { String sql = "update user set username= ? where id=?"; Object[] params = { username, id }; row = qr.update(sql, params); } catch (Exception e) { e.printStackTrace(); } return row; } //查 @Override public void selectAll() { try { String sql = "select * from user"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // qr.query(sql, rsh) //返回的是封装对象的集合 List<User> list = qr.query(sql, new BeanListHandler<User>(User.class)); for (User u : list) { System.out.println(u.getId() + "-" + u.getUsername() + "-" + u.getPassword()); } } catch (Exception e) { e.printStackTrace(); } } @Override public User selectOne(int id) { User user = null; try { String sql = "select * from user where id=?"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // qr.query(sql, rsh) //返回的是单个对象 user = qr.query(sql, new BeanHandler<User>(User.class),id); } catch (Exception e) { e.printStackTrace(); } return user; } @Override public void select(String xing) { try { String sql = "select count(*) from user where username like ?"; // 把查询的字段信息封装到对象上,再把对装到集合里面 // qr.query(sql, rsh) // 这里只能用long类型来接收,不能使用int类型 long count = qr.query(sql, new ScalarHandler<>(), xing + "%"); System.out.println(count); } catch (Exception e) { e.printStackTrace(); } } }-
User实体类
package com.qf.edu.entity; public class User { private int id; private String username; private String password; public User() { } public User(int id, String username, String password) { super(); this.id = id; this.username = username; this.password = password; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }
-
-
JDBCUtils
package com.qf.edu.utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JDBCUtils { // 1.获取连接池 public static DataSource ds = null; static { ds = new ComboPooledDataSource("c3p0"); } // 2.把连接池对象返回出去,外部需要传入一个连接池 public static DataSource getDatasource() { return ds; } // 3.建立连接 public static Connection getConnection() throws SQLException { Connection conn = ds.getConnection(); return conn; } // 4.关闭资源 public static void close(PreparedStatement ps, Connection conn) { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(PreparedStatement ps, Connection conn, ResultSet rs) { close(ps, conn); if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } } -
测试类
package com.qf.edu.test; import java.util.Scanner; import com.qf.edu.dao.JdbcDao; import com.qf.edu.dao.impl.JdbcDaoImpl; import com.qf.edu.entity.User; public class JdbcTest { public static void main(String[] args) { Scanner scanner=new Scanner(System.in); JdbcDao jd=new JdbcDaoImpl(); while (true) { System.out.println("请输入 1.添加 2.修改 3.删除 4.查询所有人 5.查询个人信息 6.查询姓X的人"); switch (scanner.next()) { case "1": System.out.println("请输入用户名:"); String username=scanner.next(); System.out.println("请输入密码:"); String password=scanner.next(); int insertRow = jd.insert(username, password); if (insertRow>0) { System.out.println("插入成功"); }else { System.out.println("插入失败"); } break; case "2": System.out.println("请输入id:"); int updateId=scanner.nextInt(); System.out.println("请输入修改的用户名:"); String updateUsername=scanner.next(); int updateRow = jd.update(updateId, updateUsername); if (updateRow>0) { System.out.println("修改成功"); }else { System.out.println("修改失败"); } break; case "3": System.out.println("请输入id:"); int deleteId=scanner.nextInt(); int deleteRow = jd.delete(deleteId); if (deleteRow>0) { System.out.println("删除成功"); }else { System.out.println("删除失败"); } break; case "4": jd.selectAll(); break; case "5": System.out.println("请输入要查的id:"); int selectOneId=scanner.nextInt(); User user = jd.selectOne(selectOneId); System.out.println(user.getId() + "-" + user.getUsername() + "-" + user.getPassword()); break; case "6": System.out.println("请输入姓X:"); String xing=scanner.next(); jd.select(xing); break; } } } }
962
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
