一、什么是JDBC
1、概念
JDBC由一组用Java语言编写的类和接口组成,是Java和数据库之间的一个桥梁,是一个规范,而不是一个实现,能够执行SQL语句。
2、各种不同类型的数据库都有相应的实现
所有不同类型数据库的开发商依照这这种规范编写了相应Java代码以提供相应的操作数据库的方法。
3、关于数据库的执行流程
二、用JDBC访问MySQL
1、配置
(1)导入相关依赖
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
(2)参数配置
pro = new Properties();
try {//参数配置
pro.load(new FileInputStream("D:\\db.properties"));
jdbcDriver = pro.getProperty("jdbcDriver");
jdbcUrl = pro.getProperty("jdbcUrl");
userName = pro.getProperty("userName");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
(3)建立连接
public void getConnection(){
try {
Class.forName(jdbcDriver);//加载驱动
connection = DriverManager.getConnection(jdbcUrl,userName,password);//建立连接
if(connection.isClosed()){
System.out.println("连接建立失败");
}
System.out.println("连接建立成功");
//获取对象,对数据库中的表进行操作时使用的对象,所有操作数据库表的方法都在这个statement之下
statement = connection.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
2、具体操作示例
(1)操作数据库的对象为Statement
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JdbcTest {
private String jdbcDriver;
private String jdbcUrl;
private String userName;
private String password;
private Properties pro;
private Connection connection;
private Statement statement; //操作数据库的对象
public JdbcTest(){
pro = new Properties();
try {//参数配置
pro.load(new FileInputStream("D:\\db.properties"));
jdbcDriver = pro.getProperty("jdbcDriver");
jdbcUrl = pro.getProperty("jdbcUrl");
userName = pro.getProperty("userName");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public void getConnection(){ //和mysql 建立连接
try {
Class.forName(jdbcDriver);//加载驱动
connection = DriverManager.getConnection(jdbcUrl,userName,password);//建立连接
if(connection.isClosed()){
System.out.println("连接建立失败");
}
System.out.println("连接建立成功");
statement = connection.createStatement();//获取对象,对数据库中的表进行操作时使用的对象,所有操作数据库表的方法都在这个statement之下
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void selectTest(){
String sql="select * from student";
//通过statement对象将sql提交给mysql执行,访问student表
try {
ResultSet resultSet = statement.executeQuery(sql);//获取结果集
//遍历结果集查看获取数据
//此处next方法类似迭代器的next和hasNext的结合实现
while(resultSet.next()){
System.out.println("stu_id:"+resultSet.getString(1)+
" name:"+resultSet.getString(2)+
" age:"+resultSet.getString(3)+
" sex:"+resultSet.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void updateTest(){
String sql="update table1 set age=20 where id=01";
try {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void insertTest(){
String sql="insert table1(id,age) values (02,31)";
try {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void deleteTest(){
String sql="delete from table1 where id=1";
try {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeConnection(){
try {
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
JdbcTest jdbcTest = new JdbcTest();
jdbcTest.getConnection();
jdbcTest.selectTest();
jdbcTest.updateTest();
jdbcTest.insertTest();
jdbcTest.deleteTest();
jdbcTest.closeConnection();
}
}
注:关于execute、executeQuery、executeUpdate
如果sql语句对数据库中的信息进行查看使用executeQuery;如果对数据库中的信息造成修改使用executeUpdate,增insert,删delete;如果不造成修改使用execute。
(2)操作数据库的对象为PreparedStatement
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JdbcPreparedTest {
private String jdbcDriver;
private String jdbcUrl;
private String userName;
private String password;
private Properties pro;
private Connection connection;
public JdbcPreparedTest(){
pro = new Properties();
try {//参数配置
pro.load(new FileInputStream("D:\\db.properties"));
jdbcDriver = pro.getProperty("jdbcDriver");
jdbcUrl = pro.getProperty("jdbcUrl");
userName = pro.getProperty("userName");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public void getConnection(){ //和mysql 建立连接
try {
Class.forName(jdbcDriver);//加载驱动
connection = DriverManager.getConnection(jdbcUrl,userName,password);//建立连接
if(connection.isClosed()){
System.out.println("连接建立失败");
}
System.out.println("连接建立成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void selectTest(){
String sql="select * from table1 where id>=?";
PreparedStatement pre=null;
try {
pre=connection.prepareStatement(sql);
pre.setInt(1,3);//查看id大于等于3的
ResultSet resultSet = pre.executeQuery();//获取结果集
//遍历结果集查看获取数据
//此处next方法类似迭代器的next和hasNext的结合实现
while(resultSet.next()) {
System.out.println("id:" + resultSet.getString(1) +
" age:" + resultSet.getString(2)); }
} catch (SQLException e) {
e.printStackTrace();
}
}
public void deleteTest(){
String sql="delete from table1 where id=3";
PreparedStatement pre=null;
try {
pre=connection.prepareStatement(sql);
//将无参的语句提交给PreparedStatement
pre.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(pre!=null){
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void insertTest(){
String sql="insert table1 values(?,?)";
PreparedStatement pre=null;
try {
connection.commit();//commit操作
connection.rollback();//rollback操作
Savepoint p1 = connection.setSavepoint("p1");//设置保存点
connection.rollback(p1);//退回至保存点
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//设置事务的隔离级别
pre=connection.prepareStatement(sql);
//将无参的语句提交给PreparedStatement
pre.setInt(1,3);//插入学号3,年龄56
pre.setInt(2,56);
pre.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(pre!=null){
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//优化后的插入方法,以提高在插入大量数据时的性能
public void insert1(){
PreparedStatement pst =null;
try {
String sql = new String("insert into table1 (id,age) values (?,?)");
pst=connection.prepareStatement(sql);
System.out.println("开始插入");
for (int i = 1; i <= 10; i++) {//插入10条进行测试
System.out.println("插入第"+i+"条");
for (int j = 1; j <2; j++) {
pst.setInt(j, i);//学号
pst.setInt(j+1, (int) (Math.random()* 100 + 1));//年龄
}
pst.executeUpdate();
}
System.out.println("插入完成。");
System.out.println();
} catch (Exception e) {
e.printStackTrace();
}finally {
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void closeConnection(){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
JdbcPreparedTest jdbcPreparedTest=new JdbcPreparedTest();
jdbcPreparedTest.getConnection();
jdbcPreparedTest.insertTest();
jdbcPreparedTest.deleteTest();
jdbcPreparedTest.selectTest();
jdbcPreparedTest.closeConnection();
}
}
(3)对比
①在执行多次相同结构的sql语句时适合使用PreparedStatement,执行单次时可使用Statement。因为Statement提交执行的是带参数的sql,可以直接得到结果,而PreparedStatement先提交一个无参的sql,再设置参数然后执行sql。
②在sql注入异常方面(利用非法的SQL拼接达到入侵数据库的目的,主要是由于程序对用户输入的数据没有进行严格的过滤,导致非法的数据库SQL操作语句的执行)
//使用Statement
public void logIn(String name,String password){
String sql="select * from table2 where name ='"+name+"' and password= '"+password+"' ";
System.out.println(sql);
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//使用PreparedStatement
public void logIn1(String name,String password){
String sql="select * from table2 where name=? and password=?";
PreparedStatement pre=null;
try {
pre = connection.prepareStatement(sql);
pre.setString(1,name);
pre.setString(2,password);
System.out.println(pre);
ResultSet resultSet = pre.executeQuery();
if(resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
此时在主函数中分别调用这两个方法:
jdbcPreparedTest.logIn("xiaoli","12345 'or' 1=1");
jdbcPreparedTest.logIn1("xiaoli","12345 'or' 1=1");
结果:
可以看出,PreparedStatement能够有效避免sql注入异常,而Statement无法避免。
三、使用JDBC操作事务
例:事务回滚
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class TranTest {
private String jdbcDriver;
private String jdbcUrl;
private String userName;
private String password;
private Properties pro;
private Connection connection;
public TranTest(){
pro = new Properties();
try {//参数配置
pro.load(new FileInputStream("D:\\db.properties"));
jdbcDriver = pro.getProperty("jdbcDriver");
jdbcUrl = pro.getProperty("jdbcUrl");
userName = pro.getProperty("userName");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public void getConnection(){ //和mysql 建立连接
try {
Class.forName(jdbcDriver);//加载驱动
connection = DriverManager.getConnection(jdbcUrl,userName,password);//建立连接
if(connection.isClosed()){
System.out.println("连接建立失败");
}
System.out.println("连接建立成功");
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void selectData(PreparedStatement pre){
try {
ResultSet resultSet = pre.executeQuery();
while(resultSet.next()) {
System.out.println("name:" + resultSet.getString(1) +
" password:" + resultSet.getString(2)); }
} catch (SQLException e) {
e.printStackTrace();
}
}
public void insertData(PreparedStatement pre){
try {
for (int i = 1; i <= 2; i++) {
for (int j = 1; j <2; j++) {
pre.setString(j, "testUser"+i);
pre.setString(j+1, "xxxyyy");
}
pre.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(pre!=null){
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void rollBackTest(){
//查看table2数据共三条,用preparedStatement提交两条数据
//再次查看表中数据
//执行rollback
String select ="select * from table2";//查询命令
String sql="insert table2 values(?,?)";//插入命令
try {
PreparedStatement selectPre=connection.prepareStatement(select);
System.out.println("第一次查询(共三条):");
selectData(selectPre);
PreparedStatement pre=connection.prepareStatement(sql);
System.out.println("\n"+"插入数据完成。");
insertData(pre);
connection.setSavepoint();
System.out.println("\n"+"设置保存点完成。");
System.out.println("\n"+"第二次查询(五条):");
selectData(selectPre);
System.out.println("\n"+"执行rollback。");
connection.rollback();
System.out.println("\n"+"第三次查询(三条):");
selectData(selectPre);
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
TranTest tranTest=new TranTest();
tranTest.getConnection();
tranTest.rollBackTest();
}
}
结果: