一、JDBC入门
想要使用JDBC就得有一个数据库,这里以mysql为例子。
获取一个mysql的jar包,复制粘贴到该文件夹下,右键bulid path安装到路径内
然后最基本的操作顺序就是加载驱动–>获取连接–>执行SQL–>释放资源。例子如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class JDBCDemo1 {
@Test
public void demo1() throws Exception{
Class.forName("com.mysql.jdbc.Driver");//加载驱动
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "");
//获得连接
Statement statement = conn.createStatement();//获得可执行sql的对象
String sql = "show tables";//sql语句
ResultSet rs = statement.executeQuery(sql);//执行sql语句
//遍历结果集
while (rs.next()) {
System.out.println(rs.getString("Tables_in_db3"));
}
//释放资源
rs.close();
statement.close();
conn.close();
}
}
二、API详解和其他基本操作
DriverManager : 驱动管理类
作用:注册驱动
这个类里面有一个静态注册驱动的方法registerDriver (Driver driver)
这个方法可以完成驱动的注册,但是实际开发中一般不会使用这个方法完成驱动的注册!
原因:
如果需要注册驱动,就会使用DriverManager.registerDriver(new Driver());
但是这个Driver类在代码中有一段静态代码块,静态代码块已经调用了注册驱动的方法。
如果再手动调用则会导致注册两次。
所以一般使用Class.forName(“com.mysql.jdbc.Driver”); 去注册驱动。
作用:获得与数据库的连接
用这个方法获得与数据库连接:getConnection (String url,String user,String password);
这个里面url的写法需要重点关注。入门中的例子里是这样的:
jdbc:mysql://localhost:3306/db3
各个的含义是:
jdbc : 连接数据库的协议
mysql : 是jdbc的子协议
localhost : 连接的MysQL 数据库服务器的主机地址。(连接是本机就可以写成localhost),如果连接不是本机的,就需要写上连接主机的IP地址。
3306 : MySQL数据库服务器的端口号+
db3 : 数据库名称
url如果连接的是本机的路径,可以简化为如下格式:
jdbc:mysql:///db3;
注意:是3个/
Connection :与数据库连接对象
作用:创建执行sql语句的对象
createStatement() : 创建一个 Statement对象来将SQL语句发送到数据库。
prepareCall(String sql) : 创建一个CallableStatement 对象来调用数据库存储过程。
prepareStatement(String sql) : 创建一个PreparedStatement对象来将参数化的SQL语句发送到数据库。
statement : 执行SQL
Callablestatement : 执行数据库中存储过程
PreparedStatement : 执行SQL对SQL进行预处理。解决SQL注入漏洞。
作用:管理事务
setAutoCommit (boolean autoCommit) : 将此连接的自动提交模式设置为给定状态。
commit() : 使所有上一次提交/回滚后进行的更改成为持久更改,并释放此Connection对象当前持有的所有数据库锁。
rollback() : 取消在当前事务中进行的所有更改,并释放此Connection对象当前持有的所有数据库锁。
Statement : 执行SQL
作用:执行SQL
boolean execute(String sql) :执行给定的SQL语句(该语句可能返回多个结果),并通知驱动程序所有自动生成的键都应该可用于获取。 执行查询,修改,添加,删除的SQL语句,如果是查询语句返回true,如果是修改,添加,删除的SQL语句,返回false。
一般使用下面两个方法:
ResultSet executeQuery(String sql) : 执行给定的SQL语句,该语句返回单个ResultSet对象。 执行查询
int executeUpdate(string sql) : 执行给定SQL语句,该语句可能为INSERT、UPDATE或 DELETE语句,或者不返回任何内容的SQL语句(如SQL DDL语句)。执行修改,添加,删除的SQL语句
作用:执行批处理
addBatch (String sql) : 将给定的SQL命令添加到此Statement对象的当前命令列表中。
**clearBatch() **: 清空此 Statement 对象的当前SQL命令列表。
int[] executeBatch() : 将一批命令提交给数据库来执行,如果全部命令执行成功,则返回更新计数组成的数组。
ResultSet : 结果集
通过查询语句获得的结果。
next() : 这个光标最早是在第一行之前,当调用了next()之后就会指向第一行。
那么结果集的获取就需要使用getxxx() 方法,如下:
getXxx(int columnlndex); 列号
getXxx(String columnName); 列名,通常是使用这个
资源释放
JDBC程序执行结束后,将与数据库进行交互的对象释放掉,通常ResultSet,Statement,Connection。这几个对象中尤其是Connection对象是非常稀有的。这个对象一定要做到尽量晚创建,尽早释放掉。
将资源释放的代码写入到 finally 的代码块中。
资源释放的代码应该写的标准:手动置为null的用处是让gc更早回收
//释放资源
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
CRUD
1.利用statement保存、修改、删除、查询操作:
保存操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCDemo2 {
@Test
public void demo1() {
Connection conn = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
statement = conn.createStatement();
String sql = new String("insert into user values(null,'小明','10086',18,'1999-01-02')");
int num = statement.executeUpdate(sql);
if (num>0) {
System.out.println("数据保存成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
此时去数据库查询表可以发现数据已经插入成果!如果出现中文乱码问题应该是数据库字符集未设置好,去手动设置一下就OK了。
修改操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCDemo2 {
@Test
public void demo1() {
Connection conn = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
statement = conn.createStatement();
String sql = "update user set username = '小美',password = '110',age=20,birthday='1999-12-05' where id = 1";
int num = statement.executeUpdate(sql);
if (num>0) {
System.out.println("数据更新成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
运行结果查询数据库如下图:
删除操作:
@Test
public void demo2() {
Connection conn = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
statement = conn.createStatement();
String sql = "delete from user where id = 2";
int num = statement.executeUpdate(sql);
if (num>0) {
System.out.println("数据删除成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
运行结果查询数据库如下图:
查询操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCDemo2 {
@Test
public void demo1() {
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
statement = conn.createStatement();
String sql = "select * from user";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getInt("age")+" "+rs.getString("birthday"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
程序运行结果如下:
2.工具类的抽取
写出上述代码后发现,代码重复且无趣,于是可以编写一个工具类方便书写,代码如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static final String driverClassName;
private static final String url;
private static final String username;
private static final String password;
static {
driverClassName = "com.mysql.jdbc.Driver";
url = "jdbc:mysql:///jdbc";
username = "root";
password = "";
}
public static void loadDriver() {
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
loadDriver();
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void release(ResultSet rs,Statement statement,Connection conn) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(Statement statement,Connection conn) {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
以后就可以利用自己的工具类去编写程序。
2.1配置信息提取到配置文件
配置文件有两种,属性文件和XML
本文采用属性文件
文件格式 : 扩展名是properties
内容: key=values
如下图:
有了配置文件后,就可以在文件中修改,那么修改上述我们创建的工具类中static的静态代码块中的部分就可以了。
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src/db.properties"));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
driverClassName = properties.getProperty("driverClassName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
3.sql注入漏洞
sql注入漏洞就是不知道你密码,但是知道用户名也可以登录你的账户,下面写一个简单的登录程序,演示这一个漏洞
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.jdbc.utils.JDBCUtils;
public class UserDemo {
private boolean login(String username,String password) {
Connection conn= null;
Statement statement= null;
ResultSet rs= null;
boolean flag = false;
try {
conn = JDBCUtils.getConnection();
statement = conn.createStatement();
String sql = "select * from user where username = '"+username+"'and password = '"+password+"'";
rs = statement.executeQuery(sql);
if (rs.next()) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, statement, conn);
}
return flag;
}
}
乍一看是没有问题,实际上只需要
输入用户名 aaa' or '1=1 或者 aaa' --
密码 随意
就可以登录aaa的账户,这就是sql注入漏洞
那么如何解决呢?
其实产生这个漏洞的原因是因为用户名使用了sql的关键字造成的
那么我们只需要采用 PreparedStatement 对象解决SQL注入漏洞。这个对象将SQL预先进行编译,使用?作为占位符。?所代表内容是SQL所固定。再次传入变量(包含SQL的关键字)。这个时候也不会识别这些关键字。代码如下:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.jdbc.utils.JDBCUtils;
public class UserDemo {
private boolean login(String username,String password) {
Connection conn= null;
PreparedStatement pStatement = null;
ResultSet rs= null;
boolean flag = false;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from user where username = ? and password = ?";
pStatement = conn.prepareStatement(sql);
pStatement.setString(1, username);
pStatement.setString(2, password);
rs = pStatement.executeQuery();
if (rs.next()) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, pStatement, conn); //pStatement 是 statement的子接口,所以利用了多态,可以释放
}
return flag;
}
}
4.利用PreparedStatement保存、修改、删除、查询操作:
保存:
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;//自己写的工具类
public class JDBCDemo {
@Test
public void demo() {
Connection conn = null;
PreparedStatement pStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into user values(?,?,?,?,?)";
pStatement = conn.prepareStatement(sql);
pStatement.setInt(1, 2);
pStatement.setString(2, "小赵");
pStatement.setString(3, "123456");
pStatement.setInt(4, 19);
pStatement.setString(5, "2001-01-01");
int num = pStatement.executeUpdate();
if (num>0) {
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(pStatement, conn);
}
}
}
修改:
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class JDBCDemo {
@Test
public void demo() {
Connection conn = null;
PreparedStatement pStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "update user set password = ? where id = ?";
pStatement = conn.prepareStatement(sql);
pStatement.setString(1, "987654");
pStatement.setInt(2, 1);
int num = pStatement.executeUpdate();
if (num>0) {
System.out.println("修改成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(pStatement, conn);
}
}
}
删除:
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class JDBCDemo {
@Test
public void demo() {
Connection conn = null;
PreparedStatement pStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "delete from user where id = ?";
pStatement = conn.prepareStatement(sql);
pStatement.setInt(1, 3);
int num = pStatement.executeUpdate();
if (num>0) {
System.out.println("删除成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(pStatement, conn);
}
}
}
查询:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class JDBCDemo {
@Test
public void demo() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from user";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getInt("age")+" "+rs.getString("birthday"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs,pStatement, conn);
}
}
}
批处理
批处理在默认情况下是关闭的,需要再url后面添加上 ?rewriteBatchedStatements=true 如下图:
批处理适合那种重复的插入视情况而看。下列是一个插入多个参数的例子:
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class JDBCDemo5 {
@Test
public void demo() {
long begin = System.currentTimeMillis();//测试耗时
Connection conn = null;
PreparedStatement pStatement = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into user values(null,?,?,?,?)";
pStatement = conn.prepareStatement(sql);
for (int i = 1; i <= 1000; i++) {
pStatement.setString(1,"name"+i);
pStatement.setString(2,i+"");
pStatement.setInt(3,i);
pStatement.setString(4, "1999-01-01");
pStatement.addBatch();
if (i%100==0) {
pStatement.executeBatch();
pStatement.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(pStatement, conn);
}
long end = System.currentTimeMillis();
System.out.println("耗时(秒):"+(end-begin)/1000);
}
}
此图仅仅展示部分
JDBC的事务管理
就是一组逻辑,要么一起成功要么一起失败,首先先在数据库中创建一个账目表,如下:
use jdbc;
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values(null,'aaa',10000);
insert into account values(null,'bbb',10000);
insert into account values(null,'ccc',10000);
在转账中没有添加事务的管理,假如转账时候出现异常,会出现 aaa账号的钱被转丢了,但是 bbb.账号的钱没有任何变化。需要给转账的功能添加事务的管理。代码如下:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class TransactionDemo1 {
@Test
public void demo1() {
Connection conn = null;
PreparedStatement pStatement = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql = "update account set money = money + ? where name = ?";
pStatement = conn.prepareStatement(sql);
pStatement.setDouble(1, -1000);
pStatement.setString(2, "aaa");
pStatement.executeUpdate();
/*假如这里有个异常,数据库的数据就不会发生变化,因为没提交,被回滚了*/
pStatement.setDouble(1, 1000);
pStatement.setString(2, "bbb");
pStatement.executeUpdate();
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.release(pStatement, conn);
}
}
}
三、连接池
连接池是创建和管理一个连接的缓冲池的技术,这些连接准备好被任何需要它们的线程使用。连接池是装有连接的容器,使用连接的话,可以从连接池中进行获取,使用完成之后将连接归还给连接池。
作用:连接对象创建和销毁是需要耗费时间的,在服务器初始化的时候就初始化一些连接。把这些连接放入到内存中,使用的时候可以从内存中获取,使用完成之后将连接放入连接池中。从内存中获取和归还的效率要远远高于创建和销毁的效率。(提升性能)。
自定义连接池的实现
1.步骤:
1.编写一个类实现DataSource
2.重写getConnection方法
3.初始化多个连接在内存中
4.编写归还连接的方法
第一个例子:
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
import com.jdbc.utils.JDBCUtils;
public class MyDataSource implements DataSource {
public List<Connection> connList = new ArrayList<Connection>();
//初始化提供连接
public MyDataSource(){
for (int i = 1; i <= 3; i++) {
connList.add(JDBCUtils.getConnection());
}
}
//从连接池获得连接
@Override
public Connection getConnection() throws SQLException {
return connList.remove(0);
}
//编写一个归还连接的方法
public void addBack(Connection conn) {
connList.add(conn);
}
//下列代码不用管
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
}
测试:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class DataSourceDemo {
@Test
public void demo1() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
MyDataSource dataSource = null;
try {
dataSource = new MyDataSource();
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (pStatement!=null) {
try {
pStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
pStatement = null;
}
dataSource.addBack(conn);
}
}
}
运行结果如下图:
说明可以使用自己创建的连接池。虽然可以使用但是还是有很多不足,这种写法不方便拓展,若用户想使用还得知道我的连接池的方法,增加了用户使用的难度。那么我们可以使用装饰者模式,对close() 进行增强.
2.利用装饰者模式对自定义连接池进行优化
装饰者模式使用条件:
增强虽的类和被增强的类实现相同的接口
在增强的类中获得被增强的类的引用
装饰者模式达到对类增强的简单例子:
interface Waiter{
public void server();
}
class Waitress implements Waiter{
public void server() {
System.out.println("服务中ing");
}
}
class WaitressWrapper implements Waiter{
private Waiter waiter;
public WaitressWrapper(Waiter waiter) {
this.waiter = waiter;
}
public void server() {
System.out.println("微笑");
waiter.server();
}
}
这样就达到了对server()方法的一个增强,服务中要先一个微笑。利用这种思路,就可以对我们的连接池进行优化。但是缺点很明显,假设接口需要重写的方法很多,为了简化编程,可以提供一个模板类(模板类原封不动的将接口中的所有方法都实现,但是都没有增强)。编写一个装饰类继承模板类。在装饰类中只需要增强某一个方法即可。
为了编写方便,我编写了一个模板类,只需要让装饰类继承模板类然后重写close方法达到不销毁,而是归还的目的。
模板类:
ConnectionWrapper类
package com.datasource.demo1;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
public class ConnectionWrapper implements Connection{
private Connection conn;
public ConnectionWrapper(Connection conn) {
this.conn = conn;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return conn.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return conn.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub
conn.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return conn.getAutoCommit();
}
@Override
public void commit() throws SQLException {
// TODO Auto-generated method stub
conn.commit();
}
@Override
public void rollback() throws SQLException {
// TODO Auto-generated method stub
conn.rollback();
}
@Override
public void close() throws SQLException {
// TODO Auto-generated method stub
conn.close();
}
@Override
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return conn.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return conn.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub
conn.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return conn.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub
conn.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return conn.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub
conn.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return conn.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return conn.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
// TODO Auto-generated method stub
conn.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement(resultSetType, resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return conn.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub
conn.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub
conn.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return conn.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return conn.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return conn.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
conn.rollback();
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
conn.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, columnNames);
}
@Override
public Clob createClob() throws SQLException {
// TODO Auto-generated method stub
return conn.createClob();
}
@Override
public Blob createBlob() throws SQLException {
// TODO Auto-generated method stub
return conn.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
// TODO Auto-generated method stub
return conn.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
// TODO Auto-generated method stub
return conn.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
// TODO Auto-generated method stub
return conn.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
// TODO Auto-generated method stub
conn.setClientInfo(name, value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
// TODO Auto-generated method stub
conn.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
// TODO Auto-generated method stub
return conn.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
// TODO Auto-generated method stub
return conn.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
// TODO Auto-generated method stub
return conn.createArrayOf(typeName, elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
// TODO Auto-generated method stub
return conn.createStruct(typeName, attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
// TODO Auto-generated method stub
conn.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
// TODO Auto-generated method stub
return conn.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
// TODO Auto-generated method stub
conn.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
// TODO Auto-generated method stub
conn.setNetworkTimeout(executor, milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
// TODO Auto-generated method stub
return conn.getNetworkTimeout();
}
}
装饰类:MyConnectionWrapper 类
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class MyConnectionWrapper extends ConnectionWrapper{
private Connection conn;
private List<Connection> connList;
public MyConnectionWrapper(Connection conn,List<Connection> connList) {
super(conn);
this.conn = conn;
this.connList = connList;
}
@Override
public void close() throws SQLException {
connList.add(conn);
}
}
MyDatesource 类
package com.datasource.demo1;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
import com.jdbc.utils.JDBCUtils;
public class MyDataSource implements DataSource {
public List<Connection> connList = new ArrayList<Connection>();
//初始化提供连接
public MyDataSource(){
for (int i = 1; i <= 3; i++) {
connList.add(JDBCUtils.getConnection());
}
}
//从连接池获得连接
@Override
public Connection getConnection() throws SQLException {
Connection conn = connList.remove(0);
MyConnectionWrapper connWraper = new MyConnectionWrapper(conn, connList);
return connWraper;
}
//下列代码不用管
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
}
DataSourceDemo 类
package com.datasource.demo1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
public class DataSourceDemo {
@Test
public void demo1() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
MyDataSource dataSource = null;
try {
dataSource = new MyDataSource();
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, pStatement, conn);//这个时候的conn传入调用是归还而不是销毁。
}
}
}
开源连接池的使用
使用maven仓库去下载且导包,本文暂不演示。
Druid
手动设置配置信息方式:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.alibaba.druid.pool.DruidDataSource;
import com.jdbc.utils.JDBCUtils;
public class DruidDemo1 {
@Test
public void name() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
DruidDataSource dataSource = new DruidDataSource();
/*---手动设置数据库的信息---*/
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///jdbc");
dataSource.setUsername("root");
dataSource.setPassword("");
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs,pStatement ,conn);//自己的工具类,但是这些开源数据库内部都是进行归还而不是销毁。
}
}
}
使用属性文件去配置信息方式:
注意:属性文件中的key要求一致
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import javax.sql.DataSource;
import org.junit.Test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jdbc.utils.JDBCUtils;
public class DruidDemo1 {
@Test
public void name() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
Properties p = new Properties();
p.load(new FileInputStream("src/db.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(p);
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs,pStatement ,conn);
}
}
}
C3P0
手动配置:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Demo1 {
@Test
public void demo1() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
/*---手动设置数据库的信息---*/
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///jdbc");
dataSource.setUser("root");
dataSource.setPassword("");
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs,pStatement ,conn);//自己的工具类,但是这些开源数据库内部都是进行归还而不是销毁。
}
}
}
使用XML去配置信息方式:
文件信息:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///jdbc</property>
<property name="user">root</property>
<property name="password"></property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!-- This app is massive! -->
<named-config name="oracle">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql::///jdbc</property>
<property name="user">root</property>
<property name="password"></property>
</named-config>
</c3p0-config>
测试方法
@Test
public void demo2() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//如果ComboPooledDataSource dataSource = new ComboPooledDataSource("oracle");
//则可以使用oracle的配置信息,若参数错误则使用默认,不会报错
/*创建连接池会自动去src下寻找c3p0-config文件*/
conn = dataSource.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs,pStatement ,conn);//自己的工具类,但是这些开源数据库内部都是进行归还而不是销毁。
}
}
根据开源连接池,优化自己写的工具类
以c3p0为例子:可以进行如下简单改写
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils2 {
private static final ComboPooledDataSource dataSoure = new ComboPooledDataSource();
public static Connection getConnection() throws SQLException {
return dataSoure.getConnection();
}
//获得连接池
public static DataSource getDataSource() {
return dataSoure;
}
public static void release(ResultSet rs,Statement statement,Connection conn) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(Statement statement,Connection conn) {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
测试方法,如下:
@Test
public void demo3() {
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
try {
conn = JDBCUtils2.getConnection();
String sql = "select * from account";
pStatement = conn.prepareStatement(sql);
rs = pStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils2.release(rs,pStatement ,conn);//自己的工具类,但是这些开源数据库内部都是进行归还而不是销毁。
}
}
四、Dbutils
是一个对JDBC进行简单封装的开源工具类库,使用它能够简化JDBC应用程序的开发,同时也不会影响程序的性能。
在一般情况下如果执行CRUD的操作:
构造:
QueryRunner(Datasource ds) ;
方法:
int update(String sql , Object…args) ;
T query(string sql , ResultSetHandler rsh,Object…args);
如果有事务管理的话使用另一套完成CRUD的操作
因为需要获取连接进行提交操作。
构造:
QueryRunner();
方法:
int update(Connection conn , string sql , Object…args) ;
T query(Connection conn, string sql , ResultSetHandler rsh,Object…args) ;
增删改操作
增加:
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils2;
public class DBUtilsDemo1 {
@Test
public void demo1() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
queryRunner.update("insert into account values(null,?,?)","ddd",10000);
}
}
运行后查看sql如图,插入成功:
修改: 原理如上只需修改即可。
queryRunner.update("update account set name=?,money=? where id=?","eee",20000,4);
删除: 同上
queryRunner.update("delete from account where id = ?",4);
查询操作
通常我们查询之后目的不仅仅是打印到控制台,而是把数据存储在对象中或者是存储到对象的集合中。
创建一个存储的实体类: Account
public class Account {
private Integer id;
private String name;
private Double money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public String toString() {
return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
}
}
查询一条记录:
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils2;
/*查询*/
public class DBUtilsDemo2 {
@Test
public void demo() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){
@Override
public Account handle(ResultSet rs) throws SQLException {
Account account = new Account();
while (rs.next()) {
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
}
return account;
}
},1);
System.out.println(account);
}
}
查询多条语句并且遍历:
测试方法如下:
@Test
public void demo2() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
List<Account> list= queryRunner.query("select * from account", new ResultSetHandler<List<Account>>(){
@Override
public List<Account> handle(ResultSet rs) throws SQLException {
List<Account> list = new ArrayList<Account>();
while (rs.next()) {
Account account = new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
list.add(account);
}
return list;
}
});
for(Account a : list) {
System.out.println(a);
}
}
运行如下图:
ResultSetHandler的实现类
利用这个知识可以帮助我们快速完成数据的封装。
1.ArrayHandler和ArrayListHandler
ArrayHandler : 将一条记录封装到一个数组当中。这个数组是object[]。
ArrayListHandler : 将多条记录封装到一个装有object[]的List集合中,即list集合中存放object[]。
测试代码如下:
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.junit.Test;
import com.jdbc.utils.JDBCUtils2;
public class DButilsDemo3 {
@Test //查询一条记录
public void demo1() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Object[] objs = queryRunner.query("select * from account where id = ?", new ArrayHandler() ,1);
System.out.println(Arrays.toString(objs));
System.out.println("------------");
}
@Test //查询多条记录
public void demo2() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
List<Object[]> list = queryRunner.query("select * from account", new ArrayListHandler());
for(Object[] obj : list) {
System.out.println(Arrays.toString(obj));
}
}
}
运行结果如下:
2.BeanHandler和BeanListHandler
BeanHandler : 将一条记录封装到一个JavaBean 中。
BeanListHandler : 将多条记录封装到一个装有JavaBean的 List集合中。
下列仅给出测试方法代码:
@Test //查询一条记录
public void demo3() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Account account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class),2);
System.out.println(account);
}
@Test //查询多条记录
public void demo4() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
List<Account> list = (List<Account>) queryRunner.query("select * from account", new BeanListHandler<Account>(Account.class));
for(Account acc : list) {
System.out.println(acc);
}
}
3.MapHandler和MapListHandler
MapHandler : 将一条记录封装到一个Map集合中,Map的 key是列名,Map的value就是表中列的记录值。
MapListHandler :将多条记录封装到一个装有Map的List集合中。
@Test //查询一条记录
public void demo5() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Map<String, Object> map = queryRunner.query("select * from account where id = ?", new MapHandler(),2);
System.out.println(map);
}
@Test //查询多条记录
public void demo6() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
List<Map<String, Object>> list = queryRunner.query("select * from account", new MapListHandler());
for(Map<String, Object> map : list) {
System.out.println(map);
}
}
4.ColumnListHandler、ScalarHandler、KeyedHandler
ColumnListHandler : 将数据中的某列封装到List集合中
ScalarHandler : 将单个值封装
KeyedHandler : 将一条记录封装到一个Map集合中。将多条记录封装到一个装有Map集合的Map集合中。而且外面的Map的key是可以指定的
@Test //查询某列记录
public void demo7() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
List<Object> list = queryRunner.query("select * from account", new ColumnListHandler<>("name"));
for(Object obj : list) {
System.out.println(obj);
}
}
@Test //查询单值记录
public void demo8() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Object obj = queryRunner.query("select count(*) from account", new ScalarHandler<>());
System.out.println(obj);
}
@Test //可以指定外面的key
public void demo9() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
Map<Object, Map<String, Object>> map = queryRunner.query("select * from account", new KeyedHandler<>("name"));
for(Object o : map.keySet()) {
System.out.println(o + "->" + map.get(o)); // 根据键值获取values,这里的values是map集合
}
}
总结
如果你看到这里,恭喜你,你会JDBC的基础操作了,多去实战吧!