JDBC 数据源使用

依赖包需求

springboot项目不需要，暂不知道是不是我的项目整合了sharding-jdbc的原因

commons-dbcp-1.4.jar
commons-pool-1.5.6.jar
ojdbc6.jar

dbcp.properties 连接配置

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.103.8:3306/WarGameConfig?useUnicode=true&characterEncoding=utf-8&useTimezone=true&serverTimezone=GMT%2B8
username=root
password=hykj2015
initialSize=10
maxActive=5
maxIdle=5
minIdle=3
maxWait=-1

JDBCPool 连接工具类

package com.hy.wargame.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Jdbc数据源连接工具类
 * 
 * @author wangsong
 * @date 2019年6月15日 下午1:27:50
 */
public class JDBCPool {

	/**
	 * 数据源
	 * 
	 * @author wangsong
	 * @date 2019年6月15日 下午1:27:25
	 * @return
	 */
	public static Connection getConn() {
		InputStream in = JDBCPool.class.getClassLoader().getResourceAsStream("dbcp.properties");
		Properties prop = new Properties();
		Connection conn = null;
		try {
			prop.load(in);
			String driverClass = prop.getProperty("driverClass");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
			// 注册驱动
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 连接数据源 - 写入sql
	 * 
	 * @author wangsong
	 * @date 2019年6月15日 下午1:26:20
	 * @param sql
	 * @return
	 */
	public static PreparedStatement getPstmt(String sql) {
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	/**
	 * 添加修改后关闭数据连接
	 * 
	 * @author wangsong
	 * @date 2019年6月15日 下午1:26:36
	 * @param ps
	 */
	public static void closeUpdateRes(PreparedStatement ps) {
		if (ps != null) {
			try {
				Connection conn = ps.getConnection();
				ps.close();
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 查询后关闭连接
	 * 
	 * @author wangsong
	 * @date 2019年6月15日 下午1:26:50
	 * @param rs
	 */
	public static void closeQueryRes(ResultSet rs) {
		if (rs != null) {
			Statement pstmt;
			try {
				pstmt = rs.getStatement();
				if (pstmt != null) {
					Connection conn = pstmt.getConnection();
					rs.close();
					pstmt.close();
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

JDBC 查询/增加/修改

id查询

/**
*根据id查询
*/
public Person getPersonById(Integer id) {
   String sql = "select * from person t where t.id = ?";
   PreparedStatement pstmt = DBUtils.getPstmt(sql);
   Person p = null;
   ResultSet rs = null;
   try {
      pstmt.setInt(1, id);
      rs = pstmt.executeQuery();
      //游标向下移动
      rs.next();
      //获得查询出来的数据
      Integer personid = rs.getInt("id");
      String name = rs.getString("name");
      String address = rs.getString("address");
      java.util.Date birthday = rs.getDate("birthday");
      //创建person对象并且赋值
      p = new Person();
      p.setId(personid);
      p.setName(name);
      p.setAddres(address);
      p.setBirthday(birthday);
      
   } catch (SQLException e) {
      e.printStackTrace();
   }finally{
      DBUtils.closeQueryRes(rs);
   }
   return p;
}

查询

/**
*查询
*/
public List<Person> listPerson() {
   String sql = "select * from person ";
   PreparedStatement pstmt = DBUtils.getPstmt(sql);
   List<Person> pList = new ArrayList<Person>();
   ResultSet rs = null;
   try {
      rs = pstmt.executeQuery();
      //游标向下移动
      while(rs.next()){
         //获得查询出来的数据
         Integer personid = rs.getInt("id");
         String name = rs.getString("name");
         String address = rs.getString("address");
         java.util.Date birthday = rs.getDate("birthday");
         //创建person对象并且赋值
         Person p = new Person();
         p.setId(personid);
         p.setName(name);
         p.setAddres(address);
         p.setBirthday(birthday);
         pList.add(p);
      }
      
   } catch (SQLException e) {
      e.printStackTrace();
   }finally{
      DBUtils.closeQueryRes(rs);
   }
   return pList;
}

添加

public void savePerson(Person p) {
   String sql = "insert into person values(personid.nextval, ?,?,?)";
   PreparedStatement pstmt = DBUtils.getPstmt(sql);
   try {
      pstmt.setString(1, p.getName());
      pstmt.setString(2, p.getAddres());
      pstmt.setDate(3, new Date(p.getBirthday().getTime()));
      pstmt.executeUpdate();
   } catch (Exception e) {
      e.printStackTrace();
   }finally{
      DBUtils.closeUpdateRes(pstmt);
   }
}

修改

/**
*修改
*/
public void updatePerson(Person p) {
   String sql = "update person t set t.name = ?, t.address = ?, t.birthday = ? where t.id = ?";
   PreparedStatement pstmt = DBUtils.getPstmt(sql);
   try {
      pstmt.setString(1, p.getName());
      pstmt.setString(2, p.getAddres());
      pstmt.setDate(3, new Date(p.getBirthday().getTime()));
      pstmt.setInt(4, p.getId());
      pstmt.executeUpdate();
   } catch (Exception e) {
      e.printStackTrace();
   }finally{
      DBUtils.closeUpdateRes(pstmt);
   }

}

大字段数据（二进制），读/写/保存硬盘

–基于查询

//二进制与大文本 添加
//预编译
String  sql = "insert into users VALUES(?,?)";
//sql执行对象
ps = conn.prepareStatement(sql);
//二进制-字节流-输入流
BufferedInputStream in1 = new BufferedInputStream(new FileInputStream("a.jpg"));
//大文本-字符流
FileReader  fr =  new FileReader("a.txt");
//二进制添加
ps.setBinaryStream(1,in1);
//大文本添加
ps.setClob(2,fr);
//添加
int count = ps.executeUpdate();
System.out.println(count);



//二进制与大文本 读取
rs = ps.executeQuery();
rs.next();
//获得二进制输入流
InputStream is = rs.getBinaryStream(1);
//获得大文本对象
Clob clob = rs.getClob(2);
//获得大文本输入流
Reader reader = clob.getCharacterStream();




//图片写入到硬盘，一般不会写到硬盘上
OutputStream out =  new FileOutputStream("c:/a.jpg");
byte [] by = new byte[1024];
int length = 0;
while ((length = is.read(by)) != -1){
    out.write(by,0,length);
}
out.flush();



//大文本写入到硬盘
FileWriter fw =  new FileWriter("a.txt");
char [] cs = new char[1024];
int length1 = 0;
while((length1 = reader.read(cs)) != -1){
    fw.write(cs,0,length1);
}
fw.flush();

时间数据

–基于查询

//====时间数据-添加
//预编译
String  sql = "insert into users VALUES(?,?,?)";
//返回对象
ps = conn.prepareStatement(sql);
java.util.Date date =new java.util.Date();
//年月日
ps.setDate(1,new Date(date.getTime()));
//年月日 时分秒
ps.setTime(2,new Time(date.getTime()));
//时间戳
ps.setTimestamp(3,new Timestamp(date.getTime()));
//响应条数+添加-修改
int num = ps.executeUpdate();
System.out.println(num);




//====时间数据-查询
while (rs.next()) {
    //年月日
    java.util.Date date1 = rs.getDate(1);
    //年月日 时分秒
    Time time =  rs.getTime(2);
    //时间戳
    Timestamp times = rs.getTimestamp(3);
    System.out.println(date1);
    System.out.println(time);
    System.out.println(times);
}

事务

//具体写法先参照增删改查
String sql = "update myaccount t set t.blance = t.blance - ? where t.id =?";
String sql2 = "update myaccount t set t.blance = t.blance + ? where t.id =?";
Connection conn = DBUtils.getConn();
PreparedStatement ps = null;
PreparedStatement ps1 = null


//如两条修改sql-如转账
ps = conn.prepareStatement(sql);  
ps1 = conn.prepareStatement(sql);


//设置事务是手动提交,让当前的连接connection的所有的数据库变更的sql都在一个事务之内，要么同时成功，要么同时失败。
conn.setAutoCommit(false);


//ps0 
ps.setBigDecimal(1, new BigDecimal(1000));
ps.setInt(2, 1);
ps.executeUpdate();
 //模拟自定义异常，会跳到catch ，catch中添加 conn.rollback(); 回滚操作
if(true){  
   throw new Exception();
}


//ps1
ps1.setBigDecimal(1, new BigDecimal(1000));
ps1.setInt(2, 2);
ps1.executeUpdate();


//手动提交事务
conn.commit();



//如果发生异常事务就回滚  - catch（还会异常）
conn.rollback();



//finally 关闭
DBUtils.closeUpdateRes(ps1);
DBUtils.closeUpdateRes(ps);

隔离级别

脏读：读到未提交更新数据(读到修改未提交的数据)
虚读（幻读）：读到已提交插入数据(两次读到数据条数不一致)
不可重复读：读到已提交更新数据（两次读到数据不一致）

conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);

   ps = conn.prepareStatement(sql);
   //隔离级别
   conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
   conn.setAutoCommit(false);
   ps.setBigDecimal(1, new BigDecimal(1000));
   ps.setInt(2, 1);
   ps.executeUpdate();
   conn.commit();