dbutils mysql_使用dbutils对mysql数据库做增删改查的基本操作方法

1、数据库名：phoenix_uml，t_user.sql 表结构

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for t_user

-- ----------------------------

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`createDate` datetime DEFAULT NULL,

`nickName` varchar(255) DEFAULT NULL,

`password` varchar(255) DEFAULT NULL,

`userName` varchar(255) NOT NULL,

`userType` int(11) NOT NULL,

`groupId` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `userName` (`userName`),

KEY `FKCB63CCB65064113` (`groupId`),

CONSTRAINT `FKCB63CCB65064113` FOREIGN KEY (`groupId`) REFERENCES `t_user_group` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2、jdbc.properties，位于工程的根目录

driverClassName = com.mysql.jdbc.Driver

password = root

username = root

url = jdbc:mysql://localhost:3306/phoenix_uml?useUnicode=true&characterEncoding=utf8

3、dbutils的测试方法

package demo14;

import java.io.FileInputStream;

import java.math.BigInteger;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.List;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import org.apache.commons.dbcp.DataSourceConnectionFactory;

import org.apache.commons.dbutils.DbUtils;

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 org.junit.After;

import org.junit.Before;

import org.junit.Test;

/**

* 本例中使用的jar包名称及版本：Commons-dbutils-1.6.jar,commons-dbcp-1.2.1.jar,commons-pool-1.6.jar

*

* 本例演示了如何使用dbutils对数据库内容做增删改查的基本操作

* @author mengfeiyang

*

*/

public class DBUtilsTest {

Connection conn = null;

DataSource ds = null;

@Before

public void test() throws Exception {

/**获取dataSource的三种方法**/

//方法一：

/*ds = new BasicDataSource();

ds.setDriverClassName("com.mysql.jdbc.Driver");

ds.setUrl("jdbc:mysql://localhost:3306/phoenix_uml?useUnicode=true&characterEncoding=utf8");

ds.setUsername("root");

ds.setPassword("root");*/

Properties pro = new Properties();

//方法二：

/*pro.setProperty("driverClassName", "com.mysql.jdbc.Driver");

pro.setProperty("url", "jdbc:mysql://localhost:3306/phoenix_uml?useUnicode=true&characterEncoding=utf8");

pro.setProperty("username", "root");

pro.setProperty("password", "123"); */

//方法3：

pro.load(new FileInputStream("jdbc.properties"));

ds = BasicDataSourceFactory.createDataSource(pro);//通过properties得到DataSource

DataSourceConnectionFactory dsc = new DataSourceConnectionFactory(ds);

conn = dsc.createConnection();

}

@Test

public void testQuery() throws SQLException{

QueryRunner qrunner = new QueryRunner();

UserModel u = qrunner.query(conn,"select * from t_user where id=?", new BeanHandler(UserModel.class),1);

System.out.println(u.getNickName()+"\t"+u.getUserName()+"\t"+u.getPassword());

}

@Test

public void testQueryList() throws SQLException{

QueryRunner qrunner = new QueryRunner();

List list = qrunner.query(conn,"select * from t_user", new BeanListHandler(UserModel.class));

for(UserModel u : list){

System.out.println(u.getNickName()+"\t"+u.getUserName()+"\t"+u.getPassword());

}

}

@Test

public void testUpdateOrDelete() throws SQLException{

QueryRunner qrunner = new QueryRunner();

qrunner.update(conn,"update t_user set nickName='张三三' where id=2");

}

@Test

public void testAdd() throws SQLException{

QueryRunner qrunner = new QueryRunner();

qrunner.insert(conn,"insert into t_user values(?,?,?,?,?,?,?);", new BeanHandler(UserModel.class), new Object[]{0,null,"马六",123,"ml",0,2});

BigInteger newId = (BigInteger) qrunner.query(conn, "select last_insert_id()", new ScalarHandler(1));

UserModel u = qrunner.query(conn, "select * from t_user where id=?",new BeanHandler(UserModel.class),newId);

System.out.println(u.getNickName()+"\t"+u.getUserName()+"\t"+u.getPassword());

}

@After

public void after(){

DbUtils.closeQuietly(conn);

}

}