文章目录
- 一、基本操作
- 1、statement
- 2、工具类
- 3、编写CRUD代码
- 二、PreparedStatement
- 三、JDBC事务操作
- 四、数据库连接池
- 1、DBCP连接池
- 2、C3P0连接池
- 3、Druid连接池
一、基本操作
1、statement
JDBC中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
- Statement对象的
executeUpdate
方法,用于向数据库发送增、删、改的SQL语句,executeUpdate
执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发送了变化)。 - Statement对象的
executeQuery
方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功~");
}
CRUD操作-delete
Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功~");
}
CRUD操作-update
Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功~");
}
CRUD操作-retrieve
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
//根据获取列的数据类型,分别调用resultSet的相应方法映射到java对象中
}
2、工具类
(1)JDBCUtils
public class JDBCUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 释放资源
*/
public static void release(Connection con, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
(2)db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
3、编写CRUD代码
(1)增
public class TestInsert {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
st = con.createStatement();
String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)\n" +
"VALUES (5,'钱七','123456','qianqi@sina.com','1988-12-04')";
int num = st.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(2)删
public class TestDelete {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
st = con.createStatement();
String sql = "DELETE FROM users WHERE `id`=5";
int num = st.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(3)改
public class TestUpdate {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
st = con.createStatement();
String sql = "UPDATE users SET birthday='1990-12-01' WHERE id=1";
int num = st.executeUpdate(sql);
if (num > 0) {
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(4)查
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
st = con.createStatement();
String sql = "SELECT * FROM users WHERE id=1";
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
二、PreparedStatement
用于解决SQL注入问题,可以预编译SQL语句。
(1)增
public class TestInsert {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 5);
st.setString(2, "钱七");
st.setString(3, "123456");
st.setString(4, "qianqi@sina.com");
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
int num = st.executeUpdate();
if (num > 0) {
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(2)删
public class TestDelete {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "DELETE FROM users WHERE `id`=?";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 5);
int num = st.executeUpdate();
if (num > 0) {
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(3)改
public class TestUpdate {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "UPDATE users SET birthday=? WHERE id=?";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
st.setInt(2, 1);
int num = st.executeUpdate();
if (num > 0) {
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(4)查
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "SELECT * FROM users WHERE id=?";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 1);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
(5)解决SQL注入问题
public class SQLQuestion {
public static void main(String[] args) {
//正常登录
//login("张三","123456");
//sql注入
login("' or '1=1", "123456");
}
/**
* 登录业务
*/
public static void login(String userName, String password) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
// PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符
// 假设其中存在转义字符,比如说'会被直接转义
String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
st = con.prepareStatement(sql);
st.setString(1, userName);
st.setString(2, password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id=" + rs.getInt("id"));
System.out.println("name=" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
三、JDBC事务操作
要么都成功,要么都失败
ACID原则
原子性:要么全部成功,要么全部失败;
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库了隔离性的问题
- 脏读:一个事务读取了另外一个没有提交的事务
- 不可重复读:在同一个事务内,重复读取表中数据,表数据发生了改变
- 幻读:在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
实现:
- 开启事务con.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚语句,但是默认失败就会回滚
四、数据库连接池
常用这三种数据库连接池:DBCP连接池、C3P0连接池、Druid连接池
数据库连接 – 执行完毕 – 释放,连接-- 释放 是十分浪费系统资源的。
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10(常用连接)
最大连接数:100 (业务最高承载上线)
等待超时:100ms
1、DBCP连接池
DBCP连接池,实现一个接口
DataSource
,需要用到的jar包 commons-dbcp-1.4 commons-pool-1.6
org.apache.commons.dbcp.BasicDataSource
(1)配置文件dbcp.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#初始化连接
initialSize=10
#最大连接数量
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED
(2)工具类JDBCDBCPUtils
public class JDBCDBCPUtils {
private static DataSource dataSource = null;
static {
try {
InputStream in = JDBCDBCPUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
//从数据源中获取连接
return dataSource.getConnection();
}
/**
* 释放资源
*/
public static void release(Connection con, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、C3P0连接池
C3P0连接池,c3p0-0.9.5.5.jar,mchange-commons-java-0.2.19.jar
com.mchange.v2.c3p0.ComboPooledDataSource
(1)配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)
-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
-->
<name-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</name-config>
</c3p0-config>
(2)工具类JDBCC3P0Utils
public class JDBCC3P0Utils {
private static DataSource dataSource = null;
//private static ComboPooledDataSource dataSource = null;
static {
try {
//代码的方式配置
// dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setJdbcUrl();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
//配置文件写法
dataSource = new ComboPooledDataSource("MySQL");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
//从数据源中获取连接
return dataSource.getConnection();
}
/**
* 释放资源
*/
public static void release(Connection con, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、Druid连接池
Druid连接池:阿里巴巴的
com.alibaba.druid.pool.DruidDataSource
要先在项目中导入mysql-connector-java-5.1.49-bin.jar
、druid-1.2.8.jar
(1)配置文件database.properties
#连接设置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/JavaTest?serverTimezone=GMT&useUnicode=true&characterEncoding=utf-8&useSSL=false
username=root
password=123456
#初始化连接数量为10个
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
maxIdle=5
#超时等待时间
maxWait=3000
(2)工具类DbUtils.java
public class DbUtils {
//声明连接池对象
private static DruidDataSource ds;
static {
Properties properties = new Properties();
InputStream is = DbUtils.class.getResourceAsStream("/database.properties");
try {
properties.load(is);
//创建连接池
ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return ds.getConnection(); //通过连接池获得连接对象
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}