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

2019独角兽企业重金招聘Python工程师标准>>>

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>(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<UserModel> list = qrunner.query(conn,"select * from t_user", new BeanListHandler<UserModel>(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>(UserModel.class), new Object[]{0,null,"马六",123,"ml",0,2});
		BigInteger newId = (BigInteger) qrunner.query(conn, "select last_insert_id()", new ScalarHandler<BigInteger>(1));  
		UserModel u = qrunner.query(conn, "select * from t_user where id=?",new BeanHandler<UserModel>(UserModel.class),newId);
		System.out.println(u.getNickName()+"\t"+u.getUserName()+"\t"+u.getPassword());
	}
	
	@After
	public void after(){
		DbUtils.closeQuietly(conn);
	}
}

 

转载于:https://my.oschina.net/u/2391658/blog/694965