一.获取数据库联机的方式:
一步步迭代,建议使用方式五
方式一:
@Test
public void ConnectionTest() throws ClassNotFoundException, SQLException {
Driver driver =new com.mysql.cj.jdbc.Driver();
String url="jdbc:mysql://localhost:3306/atguigudb?serverTimezone=UTC";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","Miaoxinyu6426.");
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
方式二:
@Test
public void testConnection2() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver =(Driver)clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/atguigudb?serverTimezone=UTC";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","Miaoxinyu6426.");
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
方式三:
@Test
public void testConnection3() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
//1.获取Driver实现类的对象
Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver =(Driver)clazz.newInstance();
//2.提供另外三个连接的基本信息
String url="jdbc:mysql://localhost:3306/atguigudb?serverTimezone=UTC";
String user="root";
String password="Miaoxinyu6426.";
//3.注册驱动
DriverManager.registerDriver(driver);
//4.获取链接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
方式四:
@Test
public void testConnection4() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
//1.提供另外三个连接的基本信息
String url="jdbc:mysql://localhost:3306/atguigudb?serverTimezone=UTC";
String user="root";
String password="Miaoxinyu6426.";
//2.获取Driver实现类的对象
Class.forName("com.mysql.cj.jdbc.Driver");
//4.获取链接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
方式五:
//最终版:将数据库连接需要的四个基本信息声明在配置文件中,通过读取配置文件的方式,获取链接
@Test
public void getConnection5() throws IOException, ClassNotFoundException, SQLException {
//1.读取配置文件中的4个基本信息
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driver=pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
此种方式的好处:
1.实现了数据与代码的分离,实现了解耦
2.如果需要修改配置文件信息,可以避免程序重新打包
二.使用PreparedStatement实现CRUD操作
2.1操作和访问数据库
向数据库中添加数据操作
public void testInsert() throws IOException, ClassNotFoundException, SQLException, ParseException {
//1.读取配置文件中的4个基本信息
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driver=pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//预编译sql语句,返回PreparedStatement实例
String sql="insert into customers(name,email,birth)values(?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
//填充占位符
ps.setString(1,"哪吒");
ps.setString(2,"dkchjdc@sjbc.com");
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("1000-01-01");
ps.setDate(3, new java.sql.Date(date.getTime()));
//执行sql语句
ps.execute();
//关闭流资源
ps.close();
connection.close();
}
增删改通用方法:
public void update(String sql,Object ...args) throws SQLException, IOException, ClassNotFoundException {
Connection connection= JDBCUtils.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
for(int i = 0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
ps.execute();
}
查的通用方法:
针对于表的字段名与类的属性名不同的情况:
1.必须声明sql时,使用类的属性名来命名字段的别名
2.使用ResultMetaData时,需要使用getColumnLabel()来替换getColumnName(),获取列的别名
各个表查询方法通用:
public static <T> T getInstance(Class<T> clazz,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException, InstantiationException {
Connection conn = JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if(rs.next()){
T t = clazz.newInstance();
for(int i = 0;i<columnCount;i++){
Object columnValue = rs.getObject(i + 1);
String columnLable = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
return null;
}
除了解决Statement的拼串,sql问题之外,PreparedStatement还有哪些好处:
1.PreparedStatement可以操作Blob数据,而Statement做不到
2.PreparedStatement可以实现更高效的批量操作
两种思想:
- 面向接口编程思想
- ORM思想:
- 一个数据表对应一个java类
- 表中的一条记录对应java的一个对象
- 表中一个字段对应java类的一个属性
两种技术:
- JDBC结果集的元数据:ResultSetMetaData
- 获取列数:getColumnCount()
- 获取列的别名:getColumnLabel()
- 通过反射,创建指定类的对象,获取指定的属性并赋值
三.操作Blob类型字段
3.1MySQL Blob类型
类型 | 大小(单位:字节) |
TinyBlob | 最大 255 |
Blob | 最大 65K |
MediumBlob | 最大 16M |
LongBlob | 最大 4G |
可能需要改max
四.实现高效批量处理
public void testInsert3() throws SQLException, IOException, ClassNotFoundException {
long start = System.currentTimeMillis();
Connection conn = JDBCUtils.getConnection();
//设置不允许自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i =0;i<1000000;i++){
ps.setObject(1,"name_"+i);
//1."攒“sql
ps.addBatch();
if(i%500==0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
//提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println(end-start);
conn.close();
ps.close();
}
五.数据库事务
5.1数据库事务介绍
1.事务:一组逻辑操作单元,是数据从一种状态变换到另一种状态
>>>一组逻辑操作单元:一个或多个DML操作
2.事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出了故障,都不能改变这种执行方式,当在一个事务中执行多个操作时,要么所有的事物都被提交,那么这些修改就永久的保存下来;要么数据库管理系统将放弃所做的所有修改,整个事务回滚到最初状态
3.数据一旦提交,就不可回滚
考虑事务处理以后的代码实现:
public class TransactionTest {
public static void main(String[] args) {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
//1.取消数据的自动提交
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("转账成功");
//2.提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
//回滚数据
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
finally {
//1.取消数据的自动提交
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void update(Connection conn,String sql,Object ...args) throws SQLException, IOException, ClassNotFoundException {
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
ps.execute();
ps.close();
}
}
5.2事物的ACID属性
1.原子性
事务中的操作要么都发生,要么都不发生
2.一致性:
事务必须从一个一致状态变换为另外一个一致状态
3.隔离性:***
4.持久性:
一个事务一旦被提交,他对数据库的改变是永久的
六.数据库连接池
6.1多种开源的数据库连接池
连接数据库:
测试c3p0数据库连接池
方式一:
public void testGetConnection() throws PropertyVetoException, SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&&rewriteBatchedStatements=true");
cpds.setUser("root");
cpds.setPassword("Miaoxinyu6426.");
cpds.setInitialPoolSize(10);
Connection conn = cpds.getConnection();
System.out.println(conn);
DataSources.destroy(cpds);
}
方式二:
@Test
public void testGetConnection1() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
Connection conn = cpds.getConnection();
System.out.println(conn);
}
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<!-- This is my config for mysql-->
<named-config name="helloc3p0">
<!-- 指定链接数据源的基本属性 -->
<property name="user">root</property>
<property name="password">Miaoxinyu6426.</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=UTC&rewriteBatchedStatements=true</property>
<!-- 若数据库中连接不足时,一次向数据库服务器申请多少个链接 -->
<property name="acquireIncrement">50</property>
<!-- 初始化数据库连接池时,连接的个数 -->
<property name="initialPoolSize">5</property>
<!-- 数据库连接池中最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 数据库连接池中最大连接数 -->
<property name="maxPoolSize">1000</property>
<!-- c3p0数据库连接池中可以维护的Statement对象的个数 -->
<property name="maxStatements">20</property>
<!-- 每个连接同时可以使用的Statement对象的个数 -->
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
测试DBCP数据库连接池:
方式一:
@Test
public void testGetConnection() throws SQLException {
BasicDataSource source = new BasicDataSource();
source.setDriverClassName("com.mysql.cj.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&&rewriteBatchedStatements=true");
source.setUsername("root");
source.setPassword("Miaoxinyu6426.");
source.setInitialSize(10);
source.setMaxActive(10);
Connection conn = source.getConnection();
System.out.println(conn);
}
方式二:
@Test
public void testGetConnection1() throws Exception {
Properties pros = new Properties();
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
pros.load(is);
DataSource source=BasicDataSourceFactory.createDataSource(pros);
Connection conn = source.getConnection();
System.out.println(conn);
}
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&&rewriteBatchedStatements=true
username=root
password=Miaoxinyu6426.
initialSize=10
测试druid数据库连接池:
@Test
public void getConnection() throws Exception {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(pros);
Connection conn = source.getConnection();
System.out.println(conn);
}
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&&rewriteBatchedStatements=true
username=root
password=Miaoxinyu6426.
driveClassName=com.mysql.cj.jdbc.Driver
initialSize=5