commons-dbutils-1.6.jar
commons-dbutils-1.6.jar
操作 JDBC 连接数据库,并且执行 sql 语句,处理返回的结果集
三个核心类:
DbUtils 关闭资源方法(安静的关闭,不用异常处理)
QueryRunner 执行 sql 语句,并且返回想要的结果集,参数 ResultSetHandler 是接口
(使用实现类,想要什么结果集,就传入该接口的实现类)
public static void insert() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "insert into sort values(null, '显示器', 5, '这是一台播放动画片显示器')";
int row = queryRunner.update(connection, sql);
if (row > 0) {
System.out.println("插入成功");
}
DbUtils.closeQuietly(connection);
}
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "update sort set sname=?,sprice=?,sdesc=? where sid=5";
Object[] params = {"咖啡", 1000, "Java 里的咖啡"};
int row = queryRunner.update(connection, sql, params);
if (row > 0) {
System.out.println("修改成功");
}
DbUtils.closeQuietly(connection);
查询操作
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Object[] rel = queryRunner.query(connection, sql, new ArrayHandler());
for (Object object : rel) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select() throws SQLException{
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object[]> query = queryRunner.query(connection, sql, new ArrayListHandler());
for (Object[] objects : query) {
for (Object object : objects) {
System.out.println(object);
}
}
DbUtils.closeQuietly(connection);
}
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Sort query = queryRunner.query(connection, sql, new BeanHandler<Sort>(Sort.class));
if (query == null) {
System.out.println("没有这条数据");
}
System.out.println(query);
DbUtils.closeQuietly(connection);
}
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Sort> query = queryRunner.query(connection, sql, new BeanListHandler<>(Sort.class));
for (Sort sort : query) {
System.out.println(sort);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object> query = queryRunner.query(connection, sql, new ColumnListHandler<>("sname"));
for (Object object : query) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select count(*) from sort";
Long query = queryRunner.query(connection, sql, new ScalarHandler<Long>());
// String sql = "select max(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
// String sql = "select avg(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
// String sql = "select sum(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
System.out.println(query);
DbUtils.closeQuietly(connection);
}
@Test
public void select7() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Map<String, Object> query = queryRunner.query(connection, sql, new MapHandler());
for (String key : query.keySet()) {
System.out.println(key + "\t" + query.get(key));
}
DbUtils.closeQuietly(connection);
}
@Test
public void select8() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Map<String, Object>> query = queryRunner.query(connection, sql, new MapListHandler());
for (Map<String, Object> map : query) {
for (String key : map.keySet()) {
System.out.println(key + "\t" + map.get(key));
}
}
DbUtils.closeQuietly(connection);
}
数据库连接池
dbinfo.properties配置文件:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:
user=root
password=123456
public class Sort {
private int sid;
private String sname;
private Double sprice;
private String sdesc;
public Sort() {
super();
}
public Sort(int sid, String sname, Double sprice, String sdesc) {
super();
this.sid = sid;
this.sname = sname;
this.sprice = sprice;
this.sdesc = sdesc;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Double getSprice() {
return sprice;
}
public void setSprice(Double sprice) {
this.sprice = sprice;
}
public String getSdesc() {
return sdesc;
}
public void setSdesc(String sdesc) {
this.sdesc = sdesc;
}
@Override
public String toString() {
return "[sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
ResourceBundle resourceBundle = ResourceBundle.getBundle("dbinfo");
driverClass = resourceBundle.getString("driverClass");
url = resourceBundle.getString("url");
user = resourceBundle.getString("user");
password = resourceBundle.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("连接失败");
}
return connection;
}
public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
connection = null;
}
}
}
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
public class DataSourceUtil {
private static BasicDataSource basicDataSource = new BasicDataSource();
static {
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc");
basicDataSource.setUsername("root");
basicDataSource.setPassword("123456");
basicDataSource.setInitialSize(10);
basicDataSource.setMaxActive(8);
basicDataSource.setMaxIdle(5);
basicDataSource.setMinIdle(2);
}
public static DataSource getDataSource() {
return basicDataSource;
}
}
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
public class Demo03 {
public static void main(String[] args) throws SQLException {
QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
String sql = "insert into sort values(null, ?, ?, ?)";
Object[] param = {"试卷", 10, "一份万分试卷"};
int row = queryRunner.update(sql, param);
if (row > 0) {
System.out.println("插入成功");
}
}
}
http://blog.csdn.net/huzongnan/article/list