JavaWeb——JDBC的使用（下）
1、操作BLOB类型字段
- 插入操作
public class BlobTest {
public static void main(String[] args) {
//插入Blob类型数据
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql="insert into customers(name,email,birth,photo)values(?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setObject(1,"dcd");
ps.setObject(2,"vip@qq.com");
ps.setObject(3,"2001-06-14");
FileInputStream fis = new FileInputStream(new File("JDBC/lib/p.png"));
ps.setBlob(4,fis);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps);
}
}
}
- 查询操作
@Test
public void query(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet=null;
FileOutputStream fos=null;
InputStream is=null;
try {
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth,photo from customers where id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1,20);
resultSet = ps.executeQuery();
if(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date birth = resultSet.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
Blob photo = resultSet.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("lib/p(1).png");
byte[] cbuf = new byte[1024];
int len;
while((len=is.read(cbuf))!=-1){
fos.write(cbuf,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2、事务
2.1、事务的代码实现
事务的ACID属性
1、原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2、一致性：事务必须使数据库从一个一致性状态变为另一个一致性状态
3、隔离性：一个事务的执行不能被其他事务干扰
4、持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的
public class ConnectionTest {
@Test
public void fun(){
Connection conn=null;
try {
conn = JDBCUtils.getConnection();
//取消数据的自动提交功能
conn.setAutoCommit(false);
String sql1="update user_table set balance=balance+100 where user=?";
update(conn,sql1,"AA");
System.out.println(10/0);
String sql2="update user_table set balance=balance-100 where user=?";
update(conn,sql2,"BB");
System.out.println("转账成功");
conn.commit();
} catch (Exception e) {
e.printStackTrace();
//如果报异常了就会发生回滚数据
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//因为之后的连接是放在数据库连接池中的，需要保证还是可以自动提交的需求，所以
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//通用增删改操作
public static void update(Connection conn,String sql,Object ...args){//占位符的个数，取决于可变形参数组的长度
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
}
}
2.2、事务并发引发的问题
有两个事务A、B。
1、脏读：对同一个数据来说，A修改了它但没有提交，B读取的时候读到了
2、不可重复读：正常情况是可以重复读的，对于同一个数据来说，B已经读取了数据，A修改了它并且提交，此时B的事务并没有结束，再次读的时候发现发生了变化。
3、幻读：事务A读取了一个字段，然后B在该表插入了一些新的行，之后如果A再读同一个表会多出几行
public class ConnectionTest {
@Test
public void update_fun(){
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql="update user_table set balance=? where user=?";
update(conn,sql,5000,"CC");
Thread.sleep(1500);
System.out.println("修改完成");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void query_fun(){
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
System.out.println(conn.getTransactionIsolation());
//修改隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
String sql="select user,password,balance from user_table where user=?";
for (User cc : getForList(conn, User.class, sql, "CC")) {
System.out.println(cc);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//通用增删改操作
public static void update(Connection conn,String sql,Object ...args){//占位符的个数，取决于可变形参数组的长度
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
}
//查找的通用函数
public <T>List<T> getForList(Connection conn,Class<T> clazz,String sql,Object ...args){
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
3、Dao模式
Database access object 数据库访问对象
DAO包下需要有下列内容
BaseDAO：是用来存放基本的用java实现的操作代码
CustomerDao和接口实现类：是用来规范格式写的接口和实现类，每个对应的类都有一对。
BaseDAO
public abstract class BaseDAO {
//通用增删改操作
public static void update(Connection conn, String sql, Object ...args){//占位符的个数，取决于可变形参数组的长度
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
}
//获得一个查询的结果
public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object ...args){
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
if(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
//查找的通用函数
public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object ...args){
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
//查询特殊值的函数
public <E> E getValue(Connection conn,String sql,Object ...args){
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
CustomerDao
public interface CustomerDao {
//将Customer对象添加到数据库中
void insert(Connection conn, Customer cust);
//根据id删除表内记录
void deleteById(Connection conn,int id);
//将指定id对象修改成指定对象
void update(Connection conn,Customer cust);
//针对指定Id查询对应的Customer对象
Customer getCustomerById(Connection conn,int id);
//查询表中的所有记录构成的集合
List<Customer> getAll(Connection conn);
//返回数据表中数据数
long getCount(Connection conn);
//
Date getMaxBirth(Connection conn);
}
CustomerDaoImpl
public class CustomerDaoImpl extends BaseDAO implements CustomerDao{
@Override
public void insert(Connection conn, Customer cust) {
String sql="insert into customers(name,email,birth)values(?,?,?)";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from customers where id=?";
update(conn,sql,id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql="update customers set name=?,email=?,birth=? where id=?";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql="select id,name,email,birth from customers where id=?";
Customer instance = getInstance(conn, Customer.class, sql, id);
return instance;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql="select id,name,email,birth from customers";
List<Customer> list = getForList(conn, Customer.class, sql);
return list;
}
@Override
public long getCount(Connection conn) {
String sql="select count(*) from customers";
long value = (long)getValue(conn, sql);
return value;
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from customers";
return getValue(conn, sql);
}
}
升级写法
BaseDao：添加了获取父类的泛型的写法
public abstract class BaseDAO<T> {
private Class<T> clazz=null;
{
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
clazz= (Class<T>) actualTypeArguments[0];
}
//通用增删改操作
public static void update(Connection conn, String sql, Object ...args){//占位符的个数，取决于可变形参数组的长度
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
}
//获得一个查询的结果
public T getInstance(Connection conn, String sql, Object ...args){
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
if(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
//查找的通用函数
public List<T> getForList(Connection conn, String sql, Object ...args){
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
//查询特殊值的函数
public <E> E getValue(Connection conn,String sql,Object ...args){
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
BaseDaoImpl：去除了使用反射类的写法，使用泛型代替。
public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDao{
@Override
public void insert(Connection conn, Customer cust) {
String sql="insert into customers(name,email,birth)values(?,?,?)";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from customers where id=?";
update(conn,sql,id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql="update customers set name=?,email=?,birth=? where id=?";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql="select id,name,email,birth from customers where id=?";
Customer instance = getInstance(conn, sql, id);
return instance;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql="select id,name,email,birth from customers";
List<Customer> list = getForList(conn, sql);
return list;
}
@Override
public long getCount(Connection conn) {
String sql="select count(*) from customers";
long value = (long)getValue(conn, sql);
return value;
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from customers";
return getValue(conn, sql);
}
}
Junit文件的生成方式
- 选中需要测试的类，右键选择go to
- 选择test，然后一步步向下就行了
- 写入测试代码即可
4、数据库连接池
- 以C3P0为例
public class C3P0Test{
@Test
public void testPool() throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test1" );
cpds.setUser("root");
cpds.setPassword("root");
cpds.setInitialPoolSize(10);
Connection conn = cpds.getConnection();
System.out.println(conn);
DataSources.destroy(cpds);//销毁连接池
}
@Test
public void testPool1() throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
Connection conn = cpds.getConnection();
System.out.println(conn);
}
}
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<named-config name="helloc3p0">
<!--提供獲取連接池的四個基本信息-->
<property name="DriverClass">com.mysql.cj.jdbc.Driver</property>
<property name="JdbcUrl">jdbc:mysql://localhost:3306/test1</property>
<property name="User">root</property>
<property name="Password">root</property>
<!--進行數據可連接池管理的基本信息-->
<!--当数据库连接池中的连接数不够时，c3p0一次性向数据库服务器申请的连接数-->
<property name="acquireIncrement">5</property>
<!--数据库连接池中初始化的连接数-->
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">100</property>
<!--最多维护的statement个数-->
<property name="maxStatements">50</property>
<!--每一个连接使用的最多的statement个数-->
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
5、DbUtils的使用
public class dbUtilsTest {
//增删改操作
@Test
public void test0(){
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnectionDruid();
FileInputStream fis = new FileInputStream(new File("lib/p.png"));
int update = runner.update(conn, "insert into customers(name,email,birth,photo)values(?,?,?,?)", "lxj", "asda@qq.com", "1997-08-09",fis);
System.out.println(update);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查询单条数据
@Test
public void test1(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
Customer query = runner.query(conn, sql, handler, 20);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查询多条数据
@Test
public void test2(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id<20?";
BeanListHandler<Customer> listHandler = new BeanListHandler<Customer>(Customer.class);
List<Customer> query = runner.query(conn, sql, listHandler, 20);
for(Customer c:query){
System.out.println(c);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//以map的形式查询单条数据
@Test
public void test3(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
MapHandler handler = new MapHandler();
Map<String, Object> map = runner.query(conn, sql, handler, 20);
System.out.println(map);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//返回特殊需求的数值
@Test
public void test4(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select count(*) from customers";
ScalarHandler handler = new ScalarHandler();
long count = (long) runner.query(conn, sql, handler);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//自定义查询方法
@Test
public void test5(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select count(*) from customers";
ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
@Override
public Customer handle(ResultSet resultSet) throws SQLException {
return null;
}
};
Customer query = runner.query(conn, sql, handler);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
667
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
