java dbutils工具类_DbUtils工具类使用

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

把相关的包引入到项目里:

9153665ce22094ad061cacfe40c16d80.png

编写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);

}

}

执行结果:

java dbutils工具类_DbUtils工具类使用_第1张图片

注意:DbUtils使用中,SQL语句中不能用单引号,我也是花了N长时间没有找到原因,至少,我是用了单引号把问号括起来一直报错.提示不需要参数,就是问号的作用没有发挥.

你可能感兴趣的