首先我们需要导入几个jar包
commons-dbutils-1.6.jar
mysql-connector-java-5.1.37-bin.jar
commons-dbcp-1.4.jar
commons-pool-1.5.6.jar
JDBC工具类
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
driverClass = rb.getString("driverClass");
url = rb.getString("url");
user = rb.getString("user");
password = rb.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;
}
}
配置文件
dbinfo.properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=123456
利用dbutilsjar包处理操作数据库后返回的结果集
commons-dbutils-1.6.jar
这个jar包是
操作JDBC连接数据库并且执行SQL语句 处理返回的结果集
三个核心类
DBUtile 关闭资源的方法(安静的关闭 不用异常处理)
QueryRunner 执行SQL语句 并且返回你想要的结果集
参数 ResultSetHandler 接口(使用实现类 想要什么结果集 就传入什么该接口的实现类)
private static void insert() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "insert into sort values(null,'显示器',5,'5块钱的显示器还用介绍?')";
int row = qr.update(connection, sql);
if (row > 0) {
System.out.println("插入成功");
}
DbUtils.closeQuietly(connection);
}
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "update sort set sname=?,sprice=?,sdesc=? where sid=5";
Object[] params = { "咖啡", 1000, "Java中的咖啡" };
int row = qr.update(connection, sql, params);
if (row > 0) {
System.out.println("修改成功");
}
DbUtils.closeQuietly(connection);
sort实体类
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 + "]";
}
}
利用jar包中的方法做查询操作
@Test
public void select1() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Object[] objects = qr.query(connection, sql, new ArrayHandler());
for (Object object : objects) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select2() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object[]> list = qr.query(connection, sql, new ArrayListHandler());
for (Object[] objects : list) {
for (Object object : objects) {
System.out.println(object);
}
System.out.println();
}
DbUtils.closeQuietly(connection);
}
@Test
public void select3() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Sort sort = qr.query(connection, sql, new BeanHandler<>(Sort.class));
if (sort == null) {
System.out.println("没有该条数据");
}
System.out.println(sort);
DbUtils.closeQuietly(connection);
}
@Test
public void select4() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Sort> list = qr.query(connection, sql, new BeanListHandler<>(Sort.class));
for (Sort sort : list) {
System.out.println(sort);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select5() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object> list = qr.query(connection, sql, new ColumnListHandler<>(2));
for (Object object : list) {
System.out.println(object);
}
}
@Test
public void select6() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select avg(sid) from sort";
BigDecimal query = qr.query(connection, sql, new ScalarHandler<BigDecimal>());
System.out.println(query.intValue());
DbUtils.closeQuietly(connection);
}
@Test
public void select7() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Map<String, Object> map = qr.query(connection, sql, new MapHandler());
for (String key : map.keySet()) {
System.out.println(key + " " + map.get(key));
}
DbUtils.closeQuietly(connection);
}
@Test
public void select8() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Map<String,Object>> list = qr.query(connection, sql, new MapListHandler());
for (Map<String, Object> map : list) {
for (String key : map.keySet()) {
System.out.println(key + " " + map.get(key));
}
}
DbUtils.closeQuietly(connection);
}
连接池
Java为了解决频繁创建数据库连接和释放数据库连接的问题
使用数据库连接池来解决
并且出了一套规范 数据库连接池规范(接口)
javax.sql.DataSource Java扩展包
有数据库厂商来提供
常用数据库连接池 DBCP C3P0
需要导入的jar包(DBCP中依赖了Pool中的类)
commons-dbcp-1.4.jar
commons-pool-1.5.6.jar
BasicDataSource 是 DataSource(规范类) 的实现类
基础设置(要使用数据库连接池 必须要设置的内容)
MySQL驱动类 com.mysql.jdbc.Driver
访问数据库连接地址
数据库访问用户名和密码
public class DataSourceUtil {
private static BasicDataSource dataSource = new BasicDataSource();
static {
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setInitialSize(10);
dataSource.setMaxActive(8);
dataSource.setMaxIdle(5);
dataSource.setMinIdle(2);
}
public static DataSource getDataSource() {
return dataSource;
}
}
测试DataSource类
public static void main(String[] args) throws SQLException {
QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
String sql = "insert into sort values(null,?,?,?)";
Object[] param = {"试卷",10,"一份万分试卷"};
int row = qr.update(sql, param);
if (row > 0) {
System.out.println("插入成功");
}
}