准备步骤:
-
创建数据库(这里只需要一个user和一个acount表)
- user包含id,username,pwd
- acount包含id,money
- 我的数据库名为mybatis,你们记得要改成自己的数据库名字(在URL那里)
- jdbc:mysql://localhost:3306/改成自己数据库名字?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8
-
创建maven项目
-
建包,我这里是com.dualseason包
一、数据库连接
-
JDBCUtil(建立com.dualseason.utils包,在里面写下面代码)
注:以下不再写包步骤,自行查看代码第一行推断包名
package com.dualseason.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCUtil { private static String driver = "com.mysql.cj.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8"; private static String user = "root"; private static String password = "你的密码"; static { try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() { try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } return null; } public static void release(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) { closeConnection(connection); closePreparedStatement(preparedStatement); closeResultSet(resultSet); } public static void release(Connection connection, PreparedStatement preparedStatement) { closeConnection(connection); closePreparedStatement(preparedStatement); } public static void closeConnection(Connection connection) { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } finally { connection = null; } } } public static void closePreparedStatement(PreparedStatement preparedStatement) { if (preparedStatement != null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { preparedStatement = null; } } } public static void closeResultSet(ResultSet resultSet) { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } finally { resultSet = null; } } } }
-
JDBCTest(以下带@Test的代码在maven项目的test目录下写)
import com.dualseason.utils.JDBCUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; public class JDBCTest { @Test public void testQuery() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JDBCUtil.getConnection(); String sql = "select * from user"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { Object id = resultSet.getObject("id"); Object username = resultSet.getObject("name"); Object password = resultSet.getObject("pwd"); System.out.println(id + " => " + username + " : " + password); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtil.release(connection, preparedStatement, resultSet); } } @Test public void testInsert(){ Connection connection = null; PreparedStatement preparedStatement =null; try { connection = JDBCUtil.getConnection(); String sql = "insert into user values('7','世杰','123456' )"; preparedStatement = connection.prepareStatement(sql); int rows = preparedStatement.executeUpdate(sql); if (rows > 0){ System.out.println("插入成功!"); }else{ System.out.println("插入失败!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement); } } @Test public void testUpdate(){ Connection connection = null; PreparedStatement preparedStatement = null; try { connection = JDBCUtil.getConnection(); String sql = "update user set name = '真*世杰' where id = '7'"; preparedStatement = connection.prepareStatement(sql); int rows = preparedStatement.executeUpdate(); if (rows > 0){ System.out.println("修改成功!"); }else{ System.out.println("修改失败!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement); } } @Test public void testDelete(){ Connection connection = null; PreparedStatement preparedStatement = null; try{ connection = JDBCUtil.getConnection(); String sql = "delete from user where mybatis.user.id = '7'"; preparedStatement = connection.prepareStatement(sql); int rows = preparedStatement.executeUpdate(); if (rows > 0){ System.out.println("删除成功!"); }else{ System.out.println("删除失败!"); } }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(connection,preparedStatement); } } }
-
数据库事务
@Test public void testTransaction() { Connection connection = null; PreparedStatement preparedStatement = null; try { connection = JDBCUtil.getConnection(); // 前提:关闭事务 connection.setAutoCommit(false); String sql = "update account set money = money - ? where id = ?"; preparedStatement = connection.prepareStatement(sql); // 扣钱, 扣ID为 1 的100块钱 preparedStatement.setInt(1, 100); preparedStatement.setInt(2, 1); preparedStatement.executeUpdate(); // 模拟异常 int a = 10 / 0; // 加钱, 给ID为 2 加100块钱 preparedStatement.setInt(1, -100); preparedStatement.setInt(2, 2); preparedStatement.executeUpdate(); // 成功:提交事务 connection.commit(); } catch (SQLException e) { try { // 失败:回滚事务 connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { JDBCUtil.release(connection, preparedStatement); } }
-
DBCP
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.7.0</version> </dependency>
①基本使用
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.dbcp2.BasicDataSource; public class DBCPTest { public static void main(String[] args) throws SQLException { BasicDataSource dataSource = new BasicDataSource(); dataSource.setUrl("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8"); dataSource.setUsername("root"); dataSource.setPassword("你的密码"); Connection connection = dataSource.getConnection(); String sql = "select * from user"; PreparedStatement prepareStatement = connection.prepareStatement(sql); ResultSet resultSet = prepareStatement.executeQuery(); while (resultSet.next()) { Object id = resultSet.getObject("id"); Object username = resultSet.getObject("name"); Object password = resultSet.getObject("pwd"); System.out.println(id + ":" + username + ":" + password); } resultSet.close(); connection.close(); dataSource.close(); } }
②高级使用
dbcp-config.properties (在src\main\resources\dbcp-config.properties路径下)
#基本设置 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8 username=root password=你的密码 #下面代码不用修改 #数据库连接池初始化数量 initialSize=10 #数据库连接池中的最大的数据库连接数 maxActive=50 #数据库连接池中的最小的数据库连接数 minIdle=5
DBCPWithConfig.java (自己看包名)
package com.dualseason.DBCP; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; public class DBCPWithConfig { public static void main(String[] args) throws Exception { BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory(); Properties properties = new Properties(); InputStream is = DBCPWithConfig.class.getClassLoader().getResourceAsStream("dbcp-config.properties"); properties.load(is); BasicDataSource dataSource = basicDataSourceFactory.createDataSource(properties); Connection connection = dataSource.getConnection(); String sql = "select * from mybatis.user"; PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ Object id = resultSet.getObject("id"); Object name = resultSet.getObject("name"); Object pwd = resultSet.getObject("pwd"); System.out.println(id + " : " + name +" : " + pwd); } resultSet.close(); connection.close(); } }
-
Druid
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.8</version> </dependency>
①基本使用
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.alibaba.druid.pool.DruidDataSource; public class DruidTest { public static void main(String[] args) throws SQLException { DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8"); dataSource.setUsername("root"); dataSource.setPassword("你的密码"); Connection connection = dataSource.getConnection(); String sql = "select * from user"; PreparedStatement prepareStatement = connection.prepareStatement(sql); ResultSet resultSet = prepareStatement.executeQuery(); while (resultSet.next()) { Object id = resultSet.getObject("id"); Object username = resultSet.getObject("name"); Object password = resultSet.getObject("pwd"); System.out.println(id + ":" + username + ":" + password); } resultSet.close(); connection.close(); dataSource.close(); } }
②高级使用
#基本设置 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8 username=root password=你的密码 #下面代码不需要修改 #数据库连接池初始化数量 initialSize=10 #数据库连接池中的最大的数据库连接数 maxActive=50 #数据库连接池中的最小的数据库连接数 minIdle=5
DruidWithConfig.java
package com.dualseason.Druid; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; public class DruidWithConfig { public static void main(String[] args) throws Exception { DruidDataSourceFactory druidDataSourceFactory = new DruidDataSourceFactory(); Properties properties = new Properties(); InputStream is = DruidWithConfig.class.getClassLoader().getResourceAsStream("druid-config.properties"); properties.load(is); DataSource dataSource = druidDataSourceFactory.createDataSource(properties); Connection connection = dataSource.getConnection(); String sql = "select * from user"; PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ Object id = resultSet.getObject("id"); Object name = resultSet.getObject("name"); Object pwd = resultSet.getObject("pwd"); System.out.println(id + " : " + name + " : " + pwd); } resultSet.close(); connection.close(); } }
-
C3P0
①基本使用
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3P0Test { public static void main(String[] args) throws SQLException { ComboPooledDataSource dataSource = new ComboPooledDataSource(); dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8"); dataSource.setUser("root"); dataSource.setPassword("你的密码"); Connection connection = dataSource.getConnection(); String sql = "select * from user"; PreparedStatement prepareStatement = connection.prepareStatement(sql); ResultSet resultSet = prepareStatement.executeQuery(); while (resultSet.next()) { Object id = resultSet.getObject("id" ); Object username = resultSet.getObject("name"); Object password = resultSet.getObject("pwd"); System.out.println(id + ":" + username + ":" + password); } resultSet.close(); connection.close(); dataSource.close(); } }
②高级使用(xml写在resources包里面,记得&要变成如下的蓝色字体,markdown无法显示我就不写了)
c3p0-config.xml(不能改名字,只能是这个名字,maven约定大于配置)
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <!-- 基本设置 --> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8</property> <property name="user">root</property> <property name="password">你的密码</property> <!-- 数据库连接池初始化数量 --> <property name="initialPoolSize">10</property> <!-- 数据库连接池中的最大的数据库连接数 --> <property name="maxPoolSize">100</property> <!-- 数据库连接池中的最小的数据库连接数 --> <property name="minPoolSize">10</property> </default-config> </c3p0-config>
c3p0WithConfig.java(可随便取名字)
package com.dualseason.c3p0; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3P0WithConfig { public static void main(String[] args) throws SQLException { ComboPooledDataSource dataSource = new ComboPooledDataSource(); Connection connection = dataSource.getConnection(); String sql = "select * from user"; PreparedStatement prepareStatement = connection.prepareStatement(sql); ResultSet resultSet = prepareStatement.executeQuery(); while (resultSet.next()) { Object id = resultSet.getObject("id"); Object username = resultSet.getObject("name"); Object password = resultSet.getObject("pwd"); System.out.println(id + ":" + username + ":" + password); } resultSet.close(); connection.close(); dataSource.close(); } }