1.jdbc进阶
jdbc事务管理
jdbc中的事务管理其实就是交给了连接对象去管理。先写一个简单的事务管理
public class Demo01 {
private static Connection conn;
private static PreparedStatement ctt;
public static void main(String[] args) {
//创建连接对象
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///数据库名",数据库用户名,数据库密码);
String sql="update user set password=password+? where username=?";
ctt = conn.prepareStatement(sql);
ctt.setString(1,"-1000");
ctt.setString(2,"刘备");
conn.setAutoCommit(false);//设置自动是否自动提交数据
ctt.executeUpdate();//执行sql语句
ctt.setString(1,"1000");//对参数进行重新修改
ctt.setString(2,"关羽");//对参数进行重新修改
ctt.executeUpdate();//再次重新执行sql语句
conn.commit();//如果执行语句错误则数据就不会提交
} catch (ClassNotFoundException e) {
try {
conn.rollback();//在提交前发生错误怎进行事务回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//释放资源
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn=null;
}
}
if (ctt!=null) {
try {
ctt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
ctt=null;
}
}
}
}
}
jdbc连接池
连接池： 是存储连接对象的容器；使用一个连接可以直接取连接 ，用完后进行销毁，提高了效率。
作用 : 解决了jdbc使用时必须重新创建 而连接池解决了重新创建的这一个过程，直接从连接池中获取。节约了时间。
bruid连接池：属于阿里巴巴团队开发jar包
环境的搭建： 需要的导包 druid-1.0.9.jar
创建连接池：
自定义设置参数：
DruidDataSource dds=new DruidDataSource();
dds.setDriverClassName("com.mysql.jdbc.Driver");//设置驱动
dds.setUrl("jdbc:mysql:///数据库名");
dds.setUsername("root");//设置数据库用户名
dds.setPassword("123");//设置用户名密码
DruidPooledConnection conn = dds.getConnection();
通过配置文件进行获取参数配置
db.properties文件内容 :
DriverClassName=com.mysql.jdbc.Driver//驱动
url=jdbc:mysql:///数据库名
username=root //数据库用户名
password=123 //数据库密码
加载配置文件
Properties pps=new Properties();
pps.load(new FileInputStream("src/db.properties"));//加载文件
DataSource ddsf=DruidDataSourceFactory.createDataSource(pps);
Connection conn = ddsf.getConnection();
c3p0连接池(3种) 必须要导入c3p0-0.9.1.2.jar
自己设置参数
ComboPooledDataSource cpd=new ComboPooledDataSource();
//driverClass
cpd.setDriverClass("com.mysql.jdbc.Driver");
//url
cpd.setJdbcUrl("jdbc:mysql:///db_test1");
//username
cpd.setUser("root");
//password
cpd.setPassword("123");
//获取连接
Connection conn = cpd.getConnection();
配置文件
properties(c3p0.properties 规定的名字不能修改)
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql:///数据库名
c3p0.user=root//数据库用户名
c3p0.password=123//数据库密码
xml(c3p0-config.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:///数据库名</property>
<property name="user">数据库用户名</property>
<property name="password">数据库密码</property>
<property name="initialPoolSize">初始化连接chi大小</property>
<property name="maxIdleTime">最大空闲时间</property>
<property name="maxPoolSize">最大连接池数</property>
<property name="minPoolSize">最小连接池数</property>
</default-config>
</c3p0-config>
获取连接
ComboPooledDataSource cpd=new ComboPooledDataSource();//c3p0会自动在src下进行自动检索 不需要手动加载 //优先级 c3p0会优先检查c3p0-config.xml文件 没有c3p0-config.xml文件则检索c3p0.properties文件
Connection conn = cpd.getConnection();
自定义连接池
连接池的定义：我们使用连接时只需要在连接池中取连接，不用时则添加到连接池中去。
创建连接对象
public class MyConnection implements Connection{
private Connection oldConnection;//获取到的连接
private LinkedList<Connection> pool;//连接池
//通过构造方法进行初始化
public MyConnection(Connection oldConnection,LinkedList<Connection> pool) {
this.oldConnection=oldConnection;
this.pool=pool;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return oldConnection.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return oldConnection.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return oldConnection.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return oldConnection.prepareStatement(sql);
}
//方法没有写完 只需要自动生成即可 ；
//这里只展示了重要的方法
}
创建连接池
/**
* 初始化： 初始化连接池的连接个数
* @author Administrator
*
*/
public class MyDataSource implements DataSource { //创建连接池对象
private static LinkedList<Connection> pool=new LinkedList<Connection>();
static{ //初始化连接池
Connection conn=null;
for (int i = 0; i < 5; i++) {
try {
conn=JdbcUtils.getConnection();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pool.add(conn);
System.out.println(conn);
}
}
@Override
public Connection getConnection() throws SQLException {
Connection conn=null;
if (pool.size()>0) {
conn = pool.removeFirst();
MyConnection mycon=new MyConnection(conn, pool);
System.out.println("获取连接");
return mycon;
}else{
new RuntimeException("服务器繁忙！");
}
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;
} @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;
} }
测试连接池:
public class Test {
public static void main(String[] args) throws SQLException {
DataSource ms=new MyDataSource();//获取数据源
Connection conn = ms.getConnection();//从数据源中获取连接
Statement ctt = conn.createStatement();//获取连接类中执行sql语句的对象
String sql="insert into user values(null,'8','3333')";//创建sql语句
int i = ctt.executeUpdate(sql);//执行sql语句
if (i>0) {
System.out.println("插入成功");
}
if (conn!=null) {//释放资源
try {
conn.close();//把连接放回集合中
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn=null;
}
}
if (ctt!=null) {
try {
ctt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
ctt=null;
}
}
}
} 2.jdbc之dbUtils
dbutils是一个apache组织开发的工具类 封装了 对数据库的增删改查操作。 大大的简化了代码的质量
增 ，删 ，改操作：
public class Demo03 {
@Test
public void method() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
int i = qr.update("增删改sql语句");//返回受影响行数
if (i>0) {
System.out.println("成功");
}
}
}
dbutils主要是对于查询语句 进行了介绍
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}
1.ResultSetHandler <类名> 返回一行数据
2.ResultSetHandler <list<类名>> 返回多行数据
使用匿名内部类 实现 handle方法
@Test //查询一行数据
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
User user = qr.query("select * from user where id=32", new ResultSetHandler<User>(){//返回一行数据
@Override
public Object handle(ResultSet rs) throws SQLException {
User u=new User();
while(rs.next()){
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassword(rs.getString("password"));
}
return u;
}});
System.out.println(user);
}
@Test //查询多行数据
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
List<User> list = qr.query("select * from user", new ResultSetHandler<List<User>>(){
@Override
public List<User> handle(ResultSet rs) throws SQLException {
List<User> list=new ArrayList<User>();
while(rs.next()){
User u=new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassword(rs.getString("password"));
list.add(u);
}
return list;
}
});
for (User user : list) {
System.out.println(user);
}
}
1.ArrayHandler() //查询一行数据，以数组的方式返回
2.ArrayListHandler() //查询多行数据 以集合的方式进行返回
@Test
/**
* 查询一行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
Object[] query = qr.query("select * from user where id=32", new ArrayHandler());
for (Object o: query) {
System.out.println(o);
}
}
@Test
/**
* 查询多行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
List<Object[]> query = qr.query("select * from user", new ArrayListHandler());
for (Object[] o: query) {
for (Object object : o) {
System.out.print(object+",");
}
System.out.println();
}
}
1.BeanHandler 返回一行数据
2.BeanListHandler 返回多行数据
@Test
/**
* 查询一行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
User user = qr.query("select * from user where id=32", new BeanHandler<User>(User.class));
System.out.println(user);
}
@Test
/**
* 查询多行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
List<User> user = qr.query("select * from user", new BeanListHandler<User>(User.class));
for (User user2 : user) {
System.out.println(user2);
}
}
1.ColumnListHandler 查询一列数据
@Test
/**
* 查询一列数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
List<Object> user = qr.query("select * from user ", new ColumnListHandler("username"));//查询username一列数据
for (Object user2 : user) {
System.out.println(user2);
}
}
2.ScalarHandler //获取一行中的一列
@Test
/**
* 查询一列数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
//查询一行中的一列内容 默认第一行第一列
Object user = qr.query("select * from user ", new ScalarHandler("username"));
System.out.println(user);
}
1.MapHandler 返回一行数据 使用键值对 键（列名） =值（列值）
2.MapListHandler 返回多行数据
@Test
/**
* 查询一行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
//查询一行中的一列内容 默认第一行第一列
Map<String, Object> map = qr.query("select * from user ", new MapHandler());
System.out.println(map);
}
@Test
/**
* 查询多行数据
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
//查询一行中的一列内容 默认第一行第一列
List<Map<String, Object>> map = qr.query("select * from user ", new MapListHandler());
for (Map<String, Object> map2 : map) {
System.out.println(map2);
}
}
1.keyedhandler 返回多条数据
@Test
/**
*
* @throws SQLException
*/
public void method1() throws SQLException{
ComboPooledDataSource cpds=new ComboPooledDataSource();
QueryRunner qr=new QueryRunner(cpds);
Map<Object, Map<String, Object>> map = qr.query("select * from user", new KeyedHandler("username")); //以username列作为最右边一列进行返回
for (Object keyo : map.keySet()) {
Set<String> set = map.get(keyo).keySet();
System.out.println(keyo+" "+map.get(keyo));
}
}
qqq{id=31, username=qqq, password=44444}
77{id=34, username=77, password=3333}
zhouxingxing{id=32, username=zhouxingxing, password=4564}
O{id=29, username=O, password=3333}
OO{id=27, username=OO, password=3333}
pPPP{id=21, username=pPPP, password=3333}
张飞{id=19, username=张飞, password=10000}
7{id=35, username=7, password=3333}
关羽{id=18, username=关羽, password=13000}
zOOO{id=22, username=zOOO, password=3333}
zO{id=30, username=zO, password=3333}
8{id=36, username=8, password=3333}
zOO{id=28, username=zOO, password=3333}
88{id=33, username=88, password=88}
zZZ{id=20, username=zZZ, password=3333}
OOO{id=25, username=OOO, password=3333}