Add, delete, modify, and query of DBUtils.

Add, delete, modify, and query of DBUtils.

Data preparation:

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;

View Code

 

Create the corresponding class:

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;    }}

View Code

 

The C3P0 connection pool is used here to extract the C3P0 tool class:

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);        }    }}

View Code

 

C3P0 configuration file: 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>

View Code

 

Test class:

Add, delete, and modify:

Package DBUtils; import java. SQL. SQLException; import org. apache. commons. dbutils. queryRunner; import org. junit. test; import demo01.C3P0Utils;/*** Test the addition, deletion, and modification operations of the DBUtils tool class */public class TestDBUtils1 {/*** add all user Methods */@ Test public void testAddUser () {try {// 1. create the core class QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write the SQL statement String SQL = "insert into users values (null ,?,?) "; // 3. set Object [] params = {"Zhang San", "6666"}; // 4. execute the add operation int rows = qr. update (SQL, params); if (rows> 0) {System. out. println ("added successfully! ");} Else {System. out. println (" failed to add! ") ;}} Catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}}/*** modify user method by id **/@ Test public void testUpdateUserById () {try {// 1. create the core class QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write SQL statement String SQL = "update users set upassword =? Where uid =? "; // 3. set the value of Object [] params = {"xxx", 1} For the slot. // 4. execute the add operation int rows = qr. update (SQL, params); if (rows> 0) {System. out. println ("modified successfully! ");} Else {System. out. println (" modification failed! ") ;}} Catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}}/*** user deletion method by id */@ Test public void testDeleteUserById () {try {// 1. create the core class QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write SQL statement String SQL = "delete from users where uid =? "; // 3. set the value of Object [] params = {19} For the slot. // 4. execute the add operation int rows = qr. update (SQL, params); if (rows> 0) {System. out. println ("deleted successfully! ");} Else {System. out. println (" deletion failed! ") ;}} Catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();}}}

View Code

Query operations are relatively complex and independent to write:

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;/*** Test DBUtils query operation */public class TestDBUtils2 {/** query all User Methods */@ Test public void testQueryAll () {try {// 1. obtain the core class queryRunner QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write the SQL statement String SQL = "select * from users"; // 3. execute the query operation List <User> users = qr. query (SQL, new BeanListHandler <User> (User. class); // 4. traverse the result set for (User user: users) {System. out. println (user. getUsername () + ":" + user. getUpassword () ;}} catch (SQLException e) {throw new RuntimeException (e) ;}/ ** query user method by id */@ Test public void testQueryUserById () {try {// 1. obtain the core class queryRunner QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write SQL statement String SQL = "select * from users where uid =? "; // 3. set the value of Object [] params = {1} For The placeholder; // 4. perform the query operation 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);}/** based on the total number of all users */@ Test public void testQueryCount () {try {// 1. obtain the core class queryRunner QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write the SQL statement String SQL = "select count (*) from users"; // 4. execute the query Operation Long count = (Long) qr. query (SQL, new ScalarHandler (); System. out. println (count);} catch (SQLException e) {throw new RuntimeException (e);}/** query all user Methods */@ Test public void testQueryAll1 () {try {// 1. obtain the core class queryRunner QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write the SQL statement String SQL = "select * from users"; // 3. execute the query operation List <Map <String, Object> list = qr. query (SQL, new MapListHandler (); // 4. traverse the result set for (Map <String, Object> map: list) {System. out. println (map) ;}} catch (SQLException e) {throw new RuntimeException (e) ;}/ ** query all user Methods */@ Test public void testQueryAll2 () {try {// 1. obtain the core class queryRunner QueryRunner qr = new QueryRunner (C3P0Utils. getDataSource (); // 2. write the SQL statement String SQL = "select * from users"; // 3. execute the query operation List <Object> list = qr. query (SQL, new ColumnListHandler ("username"); // 4. traverse the result set for (Object object: list) {System. out. println (object) ;}} catch (SQLException e) {throw new RuntimeException (e );}}}

View Code