+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
package ivyy.taobao.com.utils;
import java.sql.Connection;
import java.sql.DriverManager;
/**
*@DEMO:dbutils
*@Java：DBHelper.java
*@Date:2015-1-15下午9:24:27
*@Author:liangjilong
*@Email:jilongliang@sina.com
*@Weibo:http://weibo.com/jilongliang
*@Version:1.0
*@Description：
*/
public class DBHelper {
private static DBHelper instance = null;
private static Connection conn = null;
private static String Driver = "com.mysql.jdbc.Driver";// ConfigLoaderUtils.getProperty("jdbc.driver");
private static String Url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";// ConfigLoaderUtils.getProperty("jdbc.url");
private static String UserName = "root";// ConfigLoaderUtils.getProperty("jdbc.userName");
private static String PassWord = "root";// ConfigLoaderUtils.getProperty("jdbc.passWord");
/**建立单例模式
* Single
* @return
*/
public static DBHelper getInstance() {
if (instance == null) {
synchronized (DBHelper.class) {
instance = new DBHelper();
}
}
return instance;
}
/**
* @return
*/
public static Connection getConnection() {
try {
Class.forName(Driver);
conn = DriverManager.getConnection(Url, UserName, PassWord);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
package ivyy.taobao.com.domain;
import ivyy.taobao.com.entity.User;
import ivyy.taobao.com.utils.DBHelper;
import ivyy.taobao.com.utils.DataUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
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.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
/**
*@DEMO:dbutils
*@Java：DoMain.java
*@Date:2015-1-15下午9:20:32
*@Author:liangjilong
*@Email:jilongliang@sina.com
*@Weibo:http://weibo.com/jilongliang
*@Version:1.0
*@Description：
*/
@SuppressWarnings("all")
public class DoMain {
Connection conn=null;
/****
* 查询单条的是使用转换BeanHandler
*/
@Test
public void testQueryById(){
conn=DBHelper.getConnection();
QueryRunner qr = new QueryRunner();
String SQL="select * from user where id = ?";
User u=null;
try {
u = (User) qr.query(conn, SQL, new BeanHandler(User.class),1);
System.out.println(u.getId() + "\t" + u.getUsername() + "\t" + u.getCreateDate()+ "\t" + u.getModifyDate());
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/****
* 查询所有的是使用转换BeanListHandler
*/
@Test
public void testQueryAll(){
conn=DBHelper.getConnection();
QueryRunner qr = new QueryRunner();
String SQL="select * from user";
List<User> list=null;
try {
list = (List) qr.query(conn, SQL, new BeanListHandler(User.class));
for(User u:list){
System.out.println(u.getId() + "\t" + u.getUsername() + "\t" + u.getCreateDate());
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/***
* 通过map的key去查询
* 跟jdbc的 java.sql.ResultSet rs=null
* rs.getString("key");一样的结果
*/
@Test
public void testQueryByMapKey() {
Integer id=1;//id=1查询
conn = DBHelper.getConnection();
QueryRunner qr = new QueryRunner();
String date=DataUtil.getNowDate("yyyy-MM-dd HH:mm:ss");
try {
String Sql="update user set modifyDate ='"+date+"',username ='xiaoliang' where id ="+id;
//先将两个字段置为null
qr.update(conn, Sql);
Map<String, Object> map = qr.query(conn, "SELECT * FROM user where id = ?", new MapHandler(), id);
User u = new User();
u.setId((Integer)map.get("id"));
u.setUsername(map.get("username").toString());
u.setModifyDate(map.get("modifyDate").toString());
System.out.println(u.getId() + "\t" + u.getUsername() + "\t" + u.getModifyDate());
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/****
* 添加
*/
@Test
public void testSave() {
Long id = null;
conn = DBHelper.getConnection();
String date=DataUtil.getNowDate("yyyy-MM-dd HH:mm:ss");
String sql = "INSERT INTO user (id,username, createDate,modifyDate,type) ";
sql+="VALUES (null, 'liangjilong', '"+date+"','"+date+"','insert')";
QueryRunner qr = new QueryRunner();
try {
int res=qr.update(conn, sql);
// 获取新增记录的自增主键
id = (Long) qr.query(conn, "SELECT LAST_INSERT_ID()",new ScalarHandler(1));//获取最后一条id数据
System.out.println("更新"+res+"条数据"+"更插入的Id为"+id);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/****
* 删除
*/
@Test
public void testDelete() {
Integer id=1;//拿到最后条Id就删除掉
int res = -1;
conn = DBHelper.getConnection();
QueryRunner qr = new QueryRunner();
try {
//id=(Integer) qr.query(conn, "SELECT LAST_INSERT_ID()",new ScalarHandler(0));//获取最后一条id数据
res = qr.update(conn, "DELETE FROM user WHERE id = ?", id);
System.out.println("删除"+res+"条数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/****
* 修改
*/
@Test
public void testUpdate() {
Integer id=1;//id=1查询
conn = DBHelper.getConnection();
QueryRunner qr = new QueryRunner();
String date=DataUtil.getNowDate("yyyy-MM-dd HH:mm:ss");
try {
String Sql="update user set modifyDate ='"+date+"',username ='liangjilong' where id ="+id;
//先将两个字段置为null
int result=qr.update(conn, Sql);
System.out.println("更新"+result+"条数据");
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
DbUtils.close(conn);
//DbUtils.rollback(conn);//回滚
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package ivyy.taobao.com.entity;
import java.io.Serializable;
/**
*@DEMO:dbutils
*@Java：User.java
*@Date:2015-1-15下午9:21:34
*@Author:liangjilong
*@Email:jilongliang@sina.com
*@Weibo:http://weibo.com/jilongliang
*@Version:1.0
*@Description：
*/
public class User implements Serializable{
private Integer id;
private String username;
private String password;
private String createDate;
private String modifyDate;
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
public String getCreateDate() {
return createDate;
}
public void setCreateDate(String createDate) {
this.createDate = createDate;
}
public String getModifyDate() {
return modifyDate;
}
public void setModifyDate(String modifyDate) {
this.modifyDate = modifyDate;
}
}
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50528
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50528
File Encoding : 65001
Date: 2015-01-15 22:32:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`createDate` datetime DEFAULT NULL,
`modifyDate` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=224 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'liangjilong', 'admin123', '2014-05-28 17:38:22', null, null);
INSERT INTO `user` VALUES ('2', 'liangjilong', 'admin123', '2014-05-28 17:38:22', null, null);
INSERT INTO `user` VALUES ('3', 'liangjilong', 'admin123', '2014-05-28 17:38:22', null, null);
apache dbutils的CURD(增，修改，查询，删除)例子代码
原文：http://blog.csdn.net/jilongliang/article/details/42834875