一、JDBC对象详解
1. DriverManager:驱动管理对象
1. 注册驱动:说明程序运行时要调用哪个数据库驱动jar。
static void registerDriver(Driver driver)
:注册与给定的驱动程序 DriverManager
Class.forName("com.mysql.cj.jdbc.Driver");
2. 获取数据库连接:
- 方法:
static Connection getConnection(String url, String user, String password)
- 参数解析:
- url:指定连接的路径
- 语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
- 注意:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以写为:jdbc:mysql:///数据库名称
- user:用户名
- password:密码
- url:指定连接的路径
2. Connection:数据库连接对象
1. 获取执行sql 的对象:
Statement createStatement()
PreparedStatement prepareStatement(String sql)
2. 管理事务:
- 开启事务:
setAutoCommit(boolean autoCommit)
:调用该方法设置参数为false,即开启事务 - 提交事务:
commit()
- 回滚事务:
rollback()
3. Statement:执行sql的对象
1.执行sql
boolean execute(String sql)
:可以执行任意的sql 了解int executeUpdate(String sql)
:执行DML(insert、update、delete)语句、DDL(create,alter、drop)语句- 返回值:影响的行数。
ResultSet executeQuery(String sql)
:执行DQL(select)语句
4. ResultSet:结果集对象,封装查询结果
-
boolean next()
: 游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true -
getObject(参数):获取数据
-
Object:代表数据类型 如: int getInt() , String getString()
-
参数:
- int:代表列的编号,从1开始 如: getString(1)
- String:代表列名称。 如: getDouble(“balance”)
-
5. PreparedStatement:执行sql的对象
1. SQL注入问题
在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题.
- 输入用户随便,输入密码:
1 or 1=1
- sql:
select * from user where username = 'conan' and password =1 1=1
2. 解决sql注入问题
使用PreparedStatement对象来解决
3.预编译的SQL
参数使用?
作为占位符
二、封装工具类JDBCUtils
为了简化代码,所以我们可以把获取加载驱动,获取connect()部分的代码给抽取出来。并且配置连接数据库的信息的url,username、password也可以抽取出来放到一个jdbc.properties的配置文件中。
jdbc.properties
最新版本的mysql的驱动类是在com.mysql.cj.jdbc.Driver
,而早些版本的mysql驱动类是在com.mysql.jdbc.Driver
中。
url中的参数serverTimezone=UTC
不加可能会报错,也可以不加。
url=jdbc:mysql://localhost:3306/dbtest?serverTimezone=UTC
user=root
password=root
driver=com.mysql.cj.jdbc.Driver
JDBC.java
package utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
static{
//读取资源文件,获取值。
try {
//1. 创建Properties集合类。
Properties pro = new Properties();
//获取src路径下的文件的方式--->ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
//获取jdbc.properties的路径
//注意:路径上一定不能有中文,否则读取的时候会出错
String path = res.getPath();
//2. 加载文件
pro.load(new FileReader(path));
//3. 获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4. 注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(Statement stmt, Connection conn){
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if( rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试一下工具类是否能正常使用
JdbcTest.java
package test;
import org.junit.Test;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcTest {
@Test
public void test() {
PreparedStatement pstmt=null;
Connection connection = null;
ResultSet resultSet=null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from userInfo where id=?";
pstmt = connection.prepareStatement(sql);
/*preparedStatement中的sql占位符下标是从1开始的*/
pstmt.setInt(1,1);
resultSet= pstmt.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
System.out.println(id+" "+name+" "+email);
}
}catch (SQLException e){
}finally {
JDBCUtils.close(resultSet,pstmt, connection);
}
}
}
三、实战练习(CRUD)
因为Statement使用过程存在一些不方便,所以实际使用中一般都是使用PreparedStatement。数据库一般操作一般离不开增删改查这几个。所以接下去就按照这个顺序进行练习一下。
实验环境:JDK1.8+Mysql1.1.8
实验中使用的数据库表
create database dbtest;
use dbtest;
create table if not exists`userInfo`(
`id` int not null auto_increment,
`name` varchar(20) not null,
`email` varchar(20) not null,
primary key(`id`)
);
insert into userInfo(name,email) value('zhangsan','1136667341@qq.com');
insert into userInfo(name,email) value('zhangsan','1136667341@qq.com');
insert into userInfo(name,email) value('zhangsan','meiya@cn-meiya.com');
insert into userInfo(name,email) value('lisi','meiyuan@0757info.com');
insert into userInfo(name,email) value('wangwu','melodylu@buynow.com');
insert into userInfo(name,email) value('xiaoxiao','kevintian126@126.com');
insert into userInfo(name,email) value('xiaohie','wq901200@hotmail.com ');
insert into userInfo(name,email) value('zhouli','tony@erene.com.com');
select * from userInfo;
项目目录结构
一、Before和After注解
故名思意,一个是在测试开始之前执行的,一个是在Test结束后执行的。把会去connection和关闭操作都提取出来,这样我们之和就不需要去关心这部分代码了。
@Before
public void init() throws SQLException {
connection = JDBCUtils.getConnection();
}
@After
public void close(){
JDBCUtils.close(resultSet, pstmt, connection);
}
二、增加数据
插入一条新数据
@Test
public void testInsert() {
try {
String sql = "insert into userInfo(name,email) value (?,?)";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1,"conan");
pstmt.setString(2,"123461278@qq.com");
/*返回值是int,表示更新了几条数据*/
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
三、删除数据
把刚才插入的name为conan的数据删除
@Test
public void testDelete() {
try {
String sql = "delete from userInfo where name=?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1,"conan");
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
四、修改数据
把id为5的数据name改为conan
@Test
public void testUpdate() {
try {
String sql = "update userInfo set name=? where id=5";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1,"conan");
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
五、查询
查询name为zhangsan的信息
@Test
public void testQuery() {
try {
String sql = "select * from userInfo where name=?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "zhangsan");
ResultSet resultSet = pstmt.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
System.out.println("id: "+id+" name: "+name+" email: "+email);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
六、事务操作
如果你的 JDBC 连接是处于自动提交模式下,该模式为默认模式,那么每句 SQL 语句都是在其完成时提交到数据库。
对简单的应用程序来说这种模式相当好,但有三个原因你可能想关闭自动提交模式,并管理你自己的事务:
- 为了提高性能
- 为了保持业务流程的完整性
- 使用分布式事务
你可以通过事务在任意时间来控制以及更改应用到数据库。它把单个 SQL 语句或一组 SQL 语句作为一个逻辑单元,如果其中任一语句失败,则整个事务失败。
如果是自动提交事务的话则可能出现这样的情况,比如在一个银行账户的数据库中,需要从一个用户转账到另外一个用户。比如从用户a的余额中取出100元之后,当要转入用户b账户中的时候程序出现了错误,那这时候程序就会被终止。结果用户a的余额少了100元,而用户b的余额却没有多。为了避免这种错误,所以要关闭自动提交事务的操作。
接下来我们测试一下如果在执行过程中出现错误,那么数据库中的数据是否会发生改变。
把数据中name为zhangsan的email全改为1。人工制造出错。
操作之前的数据内容:
@Test
public void testTransaction() {
try {
/*关闭自动提交事务*/
connection.setAutoCommit(false);
String sql = "update userInfo set email=? where name=";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "1");
pstmt.setString(1, "zhangsan");
/*手动制造错误*/
int num=1/0;
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
connection.commit();
} catch (Exception e) {
try {
if(connection!=null){
connection.rollback();
}
}catch (SQLException e1){
e1.printStackTrace();
}
e.printStackTrace();
}
}
程序正如我们所料出现了除零错误,那么看下数据库数据变了没有
很明显,数据并没有发生改变。
七、总结
在使用jdbc中,可以发现,一般修改、插入、删除操作,使用的都是executeUpdate()
这个方法,返回值是成功更新是数据数量。而查询操作使用的是executeQuery()
这个方法,返回的是一个ResultSet集合。可以通过getString
、getInt
方法获取数据集。
虽然本次操作过程中使用的是Mysql数据库,但如果要操作其他数据库,比如SQL Server的话,只要导入对应的驱动jar包,然后修改一下jdbc.properties
这个配置文件的信息就行了。
四、完整代码
Github项目链接,含完整jar包和项目代码:代码。欢迎star。
JdbcTest.java
package test;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcTest {
PreparedStatement pstmt = null;
Connection connection = null;
ResultSet resultSet = null;
@Before
public void init() throws SQLException {
connection = JDBCUtils.getConnection();
}
@After
public void close() {
JDBCUtils.close(resultSet, pstmt, connection);
}
@Test
public void testInsert() {
try {
String sql = "insert into userInfo(name,email) value (?,?)";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "conan");
pstmt.setString(2, "123461278@qq.com");
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testDelete() {
try {
String sql = "delete from userInfo where name=?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "conan");
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testUpdate() {
try {
String sql = "update userInfo set name=? where id=5";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "conan");
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testQuery() {
try {
String sql = "select * from userInfo where name=?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "zhangsan");
ResultSet resultSet = pstmt.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
System.out.println("id: "+id+" name: "+name+" email: "+email);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testTransaction() {
try {
/*关闭自动提交事务*/
connection.setAutoCommit(false);
String sql = "update userInfo set email=? where name=";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "1");
pstmt.setString(1, "zhangsan");
/*手动制造错误*/
int num=1/0;
int cnt = pstmt.executeUpdate();
System.out.println(cnt);
connection.commit();
} catch (Exception e) {
try {
if(connection!=null){
connection.rollback();
}
}catch (SQLException e1){
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
JDBCUtils.java
package utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* 文件的读取,只需要读取一次即可拿到这些值。使用静态代码块
*/
static{
//读取资源文件,获取值。
try {
//1. 创建Properties集合类。
Properties pro = new Properties();
//获取src路径下的文件的方式--->ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
//获取jdbc.properties的路径
//注意:路径上一定不能有中文,否则读取的时候会出错
String path = res.getPath();
//2. 加载文件
pro.load(new FileReader(path));
//3. 获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4. 注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(Statement stmt, Connection conn){
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if( rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
jdbc.properties
url=jdbc:mysql://localhost:3306/dbtest?serverTimezone=UTC
user=root
password=root
driver=com.mysql.cj.jdbc.Driver