JDBC是SUN公司制定的一套接口,在java.sql包下。
一、准备工作
从官网拿到对应的数据库jar包,并将其配置到环境变量classpath中
这里以MySQL举例:
MySQL :: Download Connector/Jhttps://dev.mysql.com/downloads/connector/j/
下载后解压,然后配置到classpath中
二、具体操作
可以分为六个步骤
1.注册驱动
2.获取连接
3.获取数据库操作对象
4.执行SQL语句
5.处理查询结果集(仅在执行DQL语句时)
6.释放资源
1、执行insert语句
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
public class JDBCTest01 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//com.mysql.jdbc.Driver该类是在MySQLjar中,由MySQL开发人员所实现
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver); //注册驱动
String url = "jdbc:mysql://127.0.0.1:3306/lhr";
String user = "root";
String password = "3306";
conn = DriverManager.getConnection(url,user,password); //获取连接
stmt = conn.createStatement(); //获取数据库操作对象
String sql="insert into t_user (uname) values ('qwer')";
int count = stmt.executeUpdate(sql); // 执行sql语句
}catch(Exception e){
e.printStackTrace();
}finally{
// 释放资源
try{
if(stmt != null) {
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn != null) {
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
没执行前,t_user表
执行后
二、使用反射机制和资源绑定器执行update和delete语句
(1)因为在我们下载的MySQLjar包中的Driver.class中,存在静态代码块,已经替我们完成了注册驱动的步骤,使用我们只需要通过Class.forName("com.mysql.jdbc.Driver")去加载该类,执行该类中的静态代码块,就可以完成注册驱动的步骤。
(2)通过资源绑定器(java.util.ResourceBundle)将字符串存到properties文件中,来实现动态获取。在同目录下新建jdbc.properties文件
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class JDBCTest02 {
public static void main(String[] args) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn = null;
Statement stmt = null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql="update t_user set uname = 'asd' where uid = 12";
int count = stmt.executeUpdate(sql);
System.out.print(count>0 ? "Successful execution " : "failed execution");
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt != null) {
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn != null) {
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class JDBCTest02 {
public static void main(String[] args) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn = null;
Statement stmt = null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql="delete from t_user where uid = 12";
int count = stmt.executeUpdate(sql);
System.out.print(count>0 ? "Successful execution " : "failed execution");
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt != null) {
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn != null) {
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
三、执行DQL语句
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ResourceBundle;
public class JDBCTest03 {
public static void main(String[] args) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn = null;
Statement stmt = null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql="select uid, uname from t_user";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int uid = rs.getInt("uid");
String uname = rs.getString("uname");
System.out.println("|"+uid+"|"+uname+"|");
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt != null) {
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn != null) {
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
为了防止sql注入,我们在使用条件查询时获取的数据库操作对象不再是java.sql.Statement,
而是java.sql.PrepareStatement
package jdbc;
import java.sql.*;
import java.util.ResourceBundle;
public class JDBCTest04 {
public static void main(String[] args) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn = null;
PreparedStatement ps = null; //使用PrepareStatement防止条件查询时被sql注入
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
//通过预编译来防止sql注入
String sql="select uid, uname from t_user where uname = ?";
ps = conn.prepareStatement(sql);
ps.setString(1,"lhr");
ResultSet rs = ps.executeQuery();
while(rs.next()){
int uid = rs.getInt("uid");
String uname = rs.getString("uname");
System.out.println("uid = "+uid+", uname = "+uname);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(ps != null) {
ps.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn != null) {
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
对比Statement和PrepareStatement:
1.Statement存在sql注入,PrepareStatement解决了sql注入。
2.Statement编译一次执行一次,PrepareStatement编译一次执行n次,PrepareStatement效果更高。
3.PrepareStatement会在编译阶段做类型的安全检查。
四、事务提交
MySQL中的事务时自动提交的,我们可以用java.sql.Connection中的 .setAutoCommit(false)方法, 讲自动事务提交关闭,在事务结束用 .commit()方法提交事务即可。
五、在开发工具中使用
在开发工具中,我们同样应该先把对应的数据库jar导入(等同于配置classpath环境变量)
右键项目
选择Open Module Settings
选择Libraries -> 左上角“+”->选Java,导入下好的jar包即可。