数据准备:
CREATE DATABASE mybase; USE mybase; CREATE TABLE users( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64), upassword VARCHAR(64) ); INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789"); SELECT * FROM users;
创建对应的类:
package DBUtils; public class User { private int uid; private String username; private String upassword; public User() { // TODO Auto-generated constructor stub } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getUpassword() { return upassword; } public void setUpassword(String upassword) { this.upassword = upassword; } }
这里用到了C3P0连接池,抽取C3P0工具类:
package demo01; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3P0Utils { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); public static DataSource getDataSource() { return dataSource; } public static Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } }
C3P0配置文件:c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///mybase</property> <property name="user">root</property> <property name="password">xuyiqing</property> <property name="initialPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> <named-config name="yiqing"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///mybase</property> <property name="user">root</property> <property name="password">xuyiqing</property> </named-config> </c3p0-config>
测试类:
增删改:
package DBUtils; import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner; import org.junit.Test; import demo01.C3P0Utils; /** * 测试DBUtils工具类的增删改操作 */ public class TestDBUtils1 { /** * 添加所有用户方法 */ @Test public void testAddUser() { try { // 1.创建核心类QueryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写SQL语句 String sql = "insert into users values(null,?,?)"; // 3.为站位符设置值 Object[] params = { "张三", "6666" }; // 4.执行添加操作 int rows = qr.update(sql, params); if (rows > 0) { System.out.println("添加成功!"); } else { System.out.println("添加失败!"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 根据id修改用户方法 * */ @Test public void testUpdateUserById() { try { // 1.创建核心类QueryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写SQL语句 String sql = "update users set upassword=? where uid=?"; // 3.为站位符设置值 Object[] params = { "xxx", 1 }; // 4.执行添加操作 int rows = qr.update(sql, params); if (rows > 0) { System.out.println("修改成功!"); } else { System.out.println("修改失败!"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 根据id删除用户方法 */ @Test public void testDeleteUserById() { try { // 1.创建核心类QueryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写SQL语句 String sql = "delete from users where uid=?"; // 3.为站位符设置值 Object[] params = {19}; // 4.执行添加操作 int rows = qr.update(sql, params); if (rows > 0) { System.out.println("删除成功!"); } else { System.out.println("删除失败!"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
查询操作相对复杂,独立来写:
package DBUtils; import java.sql.SQLException; import java.util.List; import java.util.Map; 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.ColumnListHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.Test; import DBUtils.User; import demo01.C3P0Utils;; /** * 测试DBUtils查询操作 */ public class TestDBUtils2 { /* * 查询所有用户方法 */ @Test public void testQueryAll() { try { // 1.获取核心类queryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写sql语句 String sql = "select * from users"; // 3.执行查询操作 List<User> users = qr.query(sql, new BeanListHandler<User>(User.class)); // 4.对结果集集合进行遍历 for (User user : users) { System.out.println(user.getUsername() + " : " + user.getUpassword()); } } catch (SQLException e) { throw new RuntimeException(e); } } /* * 根据id查询用户方法 */ @Test public void testQueryUserById() { try { // 1.获取核心类queryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写sql语句 String sql = "select * from users where uid=?"; //3.为占位符设置值 Object[] params = {1}; // 4.执行查询操作 User user = qr.query(sql, new BeanHandler<User>(User.class), params); System.out.println(user.getUsername() + " : " + user.getUpassword()); } catch (SQLException e) { throw new RuntimeException(e); } } /* * 根据所有用户的总个数 */ @Test public void testQueryCount() { try { // 1.获取核心类queryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写sql语句 String sql = "select count(*) from users"; // 4.执行查询操作 Long count = (Long) qr.query(sql, new ScalarHandler()); System.out.println(count); } catch (SQLException e) { throw new RuntimeException(e); } } /* * 查询所有用户方法 */ @Test public void testQueryAll1() { try { // 1.获取核心类queryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写sql语句 String sql = "select * from users"; // 3.执行查询操作 List<Map<String, Object>> list = qr.query(sql, new MapListHandler()); // 4.对结果集集合进行遍历 for (Map<String, Object> map : list) { System.out.println(map); } } catch (SQLException e) { throw new RuntimeException(e); } } /* * 查询所有用户方法 */ @Test public void testQueryAll2() { try { // 1.获取核心类queryRunner QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); // 2.编写sql语句 String sql = "select * from users"; // 3.执行查询操作 List<Object> list = qr.query(sql, new ColumnListHandler("username")); // 4.对结果集集合进行遍历 for (Object object : list) { System.out.println(object); } } catch (SQLException e) { throw new RuntimeException(e); } } }