1.首先要导入要用的jar包
commons-dbcp-1.2.2.jar
commons-dbutils-1.2.jar
commons-pool.jar
mysql-connector-java-5.0.8-bin.jar
2.修改配置文件
参考模板:
#连接设置 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day17 username=root password=root #<!-- 初始化连接 --> initialSize=10 #最大连接数量 maxActive=50 #<!-- 最大空闲连接 --> maxIdle=20 #<!-- 最小空闲连接 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;] #注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=utf8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读(read-only)状态。 #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix) defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_COMMITTED
3.java 编写utils工具类
public class JdbcUtils {
private static DataSource datesourse;
private static Properties properties = new Properties();
static {
try {
properties.load(JdbcUtils.class.getClassLoader()
.getResourceAsStream("dbcpconfig.properties"));
BasicDataSourceFactory factory = new BasicDataSourceFactory();
datesourse = factory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return datesourse;
}
}
4.调用demo
public class Demo1 {
/*
create database day17;
use day17;
create table users(
id int primary key,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
);
*/
//使用dbutils完成数据库的crud
@Test
public void insert() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
Object params[] = {2,"bbb","123","aa@sina.com",new Date()};
runner.update(sql, params);
}
@Test
public void update() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "update users set email=? where id=?";
Object params[] = {"aaaaaa@sina.com",1};
runner.update(sql, params);
}
@Test
public void delete() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "delete from users where id=?";
runner.update(sql, 1);
}
@Test
public void find() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users where id=?";
User user = (User) runner.query(sql, 1, new BeanHandler(User.class));
System.out.println(user.getEmail());
}
@Test
public void getAll() throws Exception{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
List list = (List) runner.query(sql, new BeanListHandler(User.class));
System.out.println(list);
}
@Test
public void batch() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
Object params[][] = new Object[3][5];
for(int i=0;i<params.length;i++){ //3
params[i] = new Object[]{i+1,"aa"+i,"123",i + "@sina.com",new Date()};
}
runner.batch(sql, params);
}
}