1.JDBC(Java DataBase Connectivity[Java数据库连接]):Java语言操作关系型数据库的一套API。
2.操作步骤:
- 下载连接mysql数据库的mysql-connector-java-8.2.0.jar:MySQL :: Download Connector/J
- 利用mysql创建一个关系型数据库作为测试:(包含部门表、员工表、薪资等级表)
create database test; use test; DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP (EMPNO int(4) not null , ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE DEFAULT NULL, SAL DOUBLE(7,2), COMM DOUBLE(7,2), primary key (EMPNO), DEPTNO INT(2) ) ; CREATE TABLE SALGRADE ( GRADE INT, LOSAL INT, HISAL INT ); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); commit; INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17' , 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20' , 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22' , 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02' , 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28' , 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01' , 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09' , 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17' , 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08' , 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23' , 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03' , 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23' , 1300, NULL, 10); commit; INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit;
- 分七步利用java语言实现:1.注册驱动 2.获取连接 3.定义sql 4.获取执行sql的对象statement 5.执行sql 6.处理结果 7.释放资源。代码实现过程如下:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class jdbc_demo1 { public static void main(String[] args) throws Exception{ //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/test"; String username = "root"; String password = "sh741115"; Connection conn = DriverManager.getConnection(url,username, password); //3.定义sql String sql = "update EMP set SAL = 900.00 where EMPNO = 7369"; //4.获取执行sql的对象Statement Statement stmt = conn.createStatement(); //5.执行sql int count = stmt.executeUpdate(sql); //6.处理结果 System.out.println(count); //7.释放资源 stmt.close(); conn.close(); } }
3.API详解:
- DriverManager(驱动管理类):1.注册驱动 2.获取数据库连接
获取数据库连接的参数 url 连接路径 user 用户名 password 密码 - Connection(数据库连接对象):1.获取执行SQL对象 2.管理事务
Mysql中的事务管理:
JDBC中的事务管理:Connection接口中定义了3个对应的方法
利用Connection管理事务具体代码实现:
package sxu.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc_demo_connection {
public static void main(String[] args) throws Exception{
//1.注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "sh741115";
Connection conn = DriverManager.getConnection(url,username, password);
//3.定义sql
String sql1 = "update SALGRADE set LOSAL = 900 where GRADE = 1";
String sql2 = "update SALGRADE set LOSAL = 1200 where GRADE = 2";
//4.获取执行sql的对象Statement
Statement stmt = conn.createStatement();
try {
//开启事务
conn.setAutoCommit(false);
//5.执行sql
int count1 = stmt.executeUpdate(sql1);
//6.处理结果
System.out.println(count1);
//5.执行sql
int count2 = stmt.executeUpdate(sql2);
//6.处理结果
System.out.println(count2);
//提交事务
conn.commit();
} catch (SQLException e) {
//回滚事务
conn.rollback();
e.printStackTrace();
}
//提交事务
//7.释放资源
stmt.close();
conn.close();
}
}
- Statement:1.执行SQL语句
- ResultSet(结果集对象):1.封装了DQL查询语句的结果
获取查询结果:
- PreparedStatement:1.预编译SQL语句并执行,预防SQL注入问题
notice:SQL注入是通过操作输入来修改事先定义好的SQL语句,以达到执行代码对服务器进行攻击的方法。
PreparedStatement原理:
1.在获取PreparedStatement对象时,将sql语句发送给mysql服务器进行检查,编译(耗时长)
2.执行时不再进行这些步骤,速度更快
3.如果sql模版一样,则只需要进行一次检查、编译