DbUtils工具类使用
创建数据库
CREATE TABLE `student` (
`userId` int(11) NOT NULL,
`userName` varchar(30) NOT NULL,
`gender` char(1) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
把相关的包引入到项目里:
编写Student类:
package com.t0.test;
public class Student {
private Integer userId;
private String userName;
private String gender;
private Integer age;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "student [userId=" + userId + ", userName=" + userName + ", gender=" + gender + ", age=" + age + "]";
}
}
代码:
package com.t0.test;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class TestDbUtils {
private static Log log = LogFactory.getLog(TestDbUtils.class);
private DataSource createDataSource(Properties props) {
MysqlDataSource ds = new MysqlDataSource();
ds.setURL(props.getProperty("url"));
ds.setUser(props.getProperty("user"));
ds.setPassword(props.getProperty("password"));
return ds;
}
/**
* 创建数据源
* @return
*/
//private DataSource createDataSource() {
//MysqlDataSource ds = new MysqlDataSource();
//ds.setURL("jdbc:mysql://localhost:3306/test");
//ds.setUser("root");
//ds.setPassword("root__");
//return ds;
//}
/**
* 将查询结查返回一个数据列表List
* @param ds
*/
public void queryBeanList(DataSource ds) {
String sql = "select userId, userName, gender, age from student";
QueryRunner queryRunner = new QueryRunner(ds);
ResultSetHandler> handler = new BeanListHandler(Student.class);
List result = null;
try {
result = queryRunner.query(sql, handler);
} catch(SQLException e) {
e.printStackTrace();
}
if (null == result) {
return;
}
for (Student s : result) {
System.out.println(s);
}
}
/**
* 将查询结果返一个Bean
* @param ds
* @param id
*/
public void queryBean(DataSource ds, int id) {
String sql = "select * from student where userId = ?";
QueryRunner queryRunner = new QueryRunner(ds);
ResultSetHandler handler = new BeanHandler(Student.class);
Student result = null;
try {
result = queryRunner.query(sql, handler, id);
} catch(SQLException e) {
e.printStackTrace();
}
if (null == result) {
return;
}
System.out.println(result);
}
/**
* 将查询结果以键值对列表形式返回
* @param
*/
public void queryMapList(DataSource ds) {
String sql = "select userId, userName, gender, age from student";
QueryRunner queryRunner = new QueryRunner(ds);
ResultSetHandler>> handler = new MapListHandler();
List> result = null;
try {
result = queryRunner.query(sql, handler);
} catch (SQLException e) {
e.printStackTrace();
}
if (null == result) {
return;
}
for (Map map : result) {
System.out.println(map);
}
}
/**
* 将查询结果以键值对形式返回
* @param ds
* @param id
*/
public void queryMap(DataSource ds, int id) {
String sql = "select * from student where userId = ?";
QueryRunner queryRunner = new QueryRunner(ds);
ResultSetHandler> handler = new MapHandler();
Map result = null;
try {
result = queryRunner.query(sql, handler, id);
} catch (SQLException e) {
e.printStackTrace();
}
if (null == result) {
return;
}
System.out.println(result);
}
/**
* 自定义处理,其实也将查询结果
* @param ds
*/
public void queryCustomHandler(DataSource ds) {
String sql = "select userId, userName, gender, age from student";
QueryRunner queryRunner = new QueryRunner(ds);
ResultSetHandler> handler = new ResultSetHandler>() {
@Override
public List handle(ResultSet paramResultSet) throws SQLException {
List list = new ArrayList();
while (paramResultSet.next()) {
Student student = new Student();
student.setUserId(paramResultSet.getInt("userId"));
student.setUserName(paramResultSet.getString("userName"));
student.setGender(paramResultSet.getString("gender"));
student.setAge(paramResultSet.getInt("age"));
list.add(student);
}
return list;
}
};
List result = null;
try {
result = queryRunner.query(sql, handler);
} catch (SQLException e) {
e.printStackTrace();
}
if (null == result ) {
return;
}
for (Student student : result) {
System.out.println(student);
}
}
public int updateBean(DataSource ds,Student s) {
int n = 0;
String sql = "insert into student(userId, userName, gender, age) values('?','?','?','?');";
QueryRunner queryRunner = new QueryRunner(ds);
try {
n = queryRunner.update(sql,s.getUserId(),s.getUserName(), s.getGender(), s.getAge() );
if (0 == n) {
return 0;
}
System.out.println("插入了 " + n + " 个数据!" );
} catch (SQLException e) {
e.printStackTrace();
}
return n;
}
public int insertBean(DataSource ds, Student s) {
int n = 0;
String sql = "insert into student(userId, userName, gender, age) values(?,?,?,?)";
QueryRunner queryRunner = new QueryRunner(ds);
try {
//n = queryRunner.update(sql, new Object[] {"22", "二2222", "M","22"} );
n = queryRunner.update(sql,new Object[] {s.getUserId(), s.getUserName(), s.getGender(), s.getAge()});
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return n;
}
public static void main(String[] args) {
Properties properties = new Properties();
properties.setProperty("url", "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8");
properties.setProperty("user", "root");
properties.setProperty("password", "root__");
// TODO Auto-generated method stub
TestDbUtils tDbUtils = new TestDbUtils();
//DataSource ds = tDbUtils.createDataSource();
DataSource ds = tDbUtils.createDataSource(properties);
System.out.println("-------queryBeanList--------");
tDbUtils.queryBeanList(ds);
System.out.println("-------queryBean--------");
tDbUtils.queryBean(ds, 5);
System.out.println("-------queryMapList--------");
tDbUtils.queryMapList(ds);
System.out.println("-------queryMap--------");
tDbUtils.queryMap(ds, 6);
System.out.println("-------queryCustomHandler-------");
tDbUtils.queryCustomHandler(ds);
System.out.println("============================================");
Student s = new Student();
s.setUserId(13);
s.setUserName("十3郎");
s.setGender("M");
s.setAge(34);
tDbUtils.insertBean(ds, s);
}
}
执行结果:
注意:DbUtils使用中,SQL语句中不能用单引号,我也是花了N长时间没有找到原因,至少,我是用了单引号把问号括起来一直报错.提示不需要参数,就是问号的作用没有发挥.
