1)步骤
1.导入驱动jar包
2.注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
3.获取数据库连接对象Connection
Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/books”, “root”, “password”);
4.定义SQL
5.获取执行SQL语句的对象Statement
Statement statement = connection.createStatement();
6.执行SQL，接收返回结果
statement.executeUpdate(sql);
7.处理结果
8.释放资源
statement.close();
connection.close();
2）详解各个对象
1.DriverManager:驱动管理对象
功能：
1.注册驱动：告诉程序该使用哪一个数据驱动jar
2.获取数据库连接
方法：static Connection getConnection(String url,String user,String password);
参数：
url:指定连接的路径
语法：jdbc:mysql://ip地址（域名）:端口号/数据库名称
2.Connection:数据库连接对象
功能：
1.获取执行SQL的对象
Statement createStatement();
Preparedstatement peparedstatement(String sql)
2.管理事务：
开启事务：setAutoCommit(boolean autoCommit):调用该方法设置参数为false，即开启事务
提交事务：commit()
回滚事务：rollback()
3.Statement:执行SQL
1.执行SQL
1.boolean execute(String sql)：可以执行任意的SQL
2.int executeUpdate(String sql):执行DML（insert、update、dalete）语句、DDL（create、alter、drop）语句
返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功，反之。
3.resultSet executeQuery(String sql):执行DQL（select）语句
练习：
```java
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args){
/* Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "root");
String sql = "INSERT INTO booktype VALUES(4'科技')";
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
connection.close();
*/
Connection connection= null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "root");
statement = connection.createStatement();
/* String sql="insert into booktype values(4,'科技";
int count = statement.executeUpdate(sql);
System.out.println(count);
if(count>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
*/
/*
String sql="delete from booktype where tid = '4'";
int count = statement.executeUpdate(sql);
System.out.println(count);
if(count>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
*/
String sql="update book set press='清华大学出版社' where bid='2'";
int count = statement.executeUpdate(sql);
System.out.println(count);
if(count>0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
4.Resultset:结果集对象，封装查询结果
next():游标向下移动一行
getXxx(参数):获取数据
Xxx:代表数据类型 如：int getInt(),String getString()
参数：
1.int:代表列的编号，从1开始 如：getString(1)
2.String:代表列的名称 如：getDouble(“balance”)
注意：
使用步骤
1.游标向下移动一行
2.判断是否有数据
3.获取数据
练习：
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args){
Connection connection= null;
Statement statement = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "root");
statement = connection.createStatement();
String sql="select * from book";
rs = statement.executeQuery(sql);
while(rs.next()) {
int bid = rs.getInt("bid");
String name = rs.getString("name");
String card = rs.getString("card");
String autho = rs.getString("autho");
String num = rs.getString("num");
String press = rs.getString("press");
String type = rs.getString("type");
System.out.println(bid+" "+name+" "+card+" "+autho+" "+num+" "+press+" "+type);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
练习：
查询book表的数据将其封装为对象，然后装载集合，返回
1.定义Book类
2.定义方法 public List<‘Book’> findAll(){}
3.实现方法 select * from book;
package Bean;
public class Book {
private int bid;
private String name;
private String card;
private String autho;
private int num;
private String press;
private String type;
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCard() {
return card;
}
public void setCard(String card) {
this.card = card;
}
public String getAutho() {
return autho;
}
public void setAutho(String autho) {
this.autho = autho;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getPress() {
return press;
}
public void setPress(String press) {
this.press = press;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
@Override
public String toString() {
return "book [bid=" + bid + ", name=" + name + ", card=" + card + ", autho=" + autho + ", num=" + num
+ ", press=" + press + ", type=" + type + "]";
}
}
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Bean.Book;
public class BookJDBCTest {
public static void main(String[] args) {
List<Book> list = new BookJDBCTest().findAll();
System.out.println(list);
}
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
Book book = null;
List<Book> list = null;
public List<Book> findAll(){
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "Xing4g.qq.com.");
statement = connection.createStatement();
String sql = "select * from book";
rs = statement.executeQuery(sql);
list = new ArrayList<Book>();
while(rs.next()) {
int bid = rs.getInt("bid");
String name = rs.getString("name");
String card = rs.getString("card");
String autho = rs.getString("autho");
int num = rs.getInt("num");
String press = rs.getString("press");
String type = rs.getString("type");
book = new Book();
book.setBid(bid);
book.setName(name);
book.setCard(card);
book.setAutho(autho);
book.setNum(num);
book.setPress(press);
book.setType(type);
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
}
**抽取JDBC工具类：JDBCUtils
目的：建华书写·简化书写
分析：
1.注册驱动抽取
2.抽取一个方法获取链接对象
需求：不想传递参数，还得保证工具类的通用性
解决：配置文件
jdbc.properties
url
user
password
3.抽取一个方法释放资源
package util;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 properties = new Properties();
//获取src路径下的文件的方式--->ClassLoader类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//2.加载文件
properties.load(new FileReader(path));
//3.获取数据，赋值
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//4.注册驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
//释放资源
public static void close(Connection connection,Statement statement,ResultSet rs) {
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Bean.Book;
public class UtilBookJDBCTest {
public static void main(String[] args) {
List<Book> list = new UtilBookJDBCTest().findAll();
System.out.println(list);
}
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
Book book = null;
List<Book> list = null;
public List<Book> findAll(){
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from book";
rs = statement.executeQuery(sql);
list = new ArrayList<Book>();
while(rs.next()) {
int bid = rs.getInt("bid");
String name = rs.getString("name");
String card = rs.getString("card");
String autho = rs.getString("autho");
int num = rs.getInt("num");
String press = rs.getString("press");
String type = rs.getString("type");
book = new Book();
book.setBid(bid);
book.setName(name);
book.setCard(card);
book.setAutho(autho);
book.setNum(num);
book.setPress(press);
book.setType(type);
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(connection, statement, rs);
}
return list;
}
}
练习：
*需求：
1.通过键盘录入用户名和密码
2.判断用户是否登录
package JDBC;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import util.JDBCUtils;
public class JudgeLogin {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
boolean result = new JudgeLogin().login(username, password);
if(result == true) {
System.out.println("登陆成功");
}else {
System.out.println("用户名或密码错误");
}
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
public boolean login(String username,String password) {
if(username == null || password == null) {
return false;
}
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from admin where username = '"+username+"' and password = '"+password+"'";
resultSet = statement.executeQuery(sql);
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(connection, statement, resultSet);
}
return false;
}
}
5.PreparedStatement：执行SQL的对象
1.SQL注入问题：在拼接SQL时，有一些SQL的特殊关键字参与字符串的拼接，造成安全性问题
2.解决SQL注入问题：使用PreparedStatement对象来解决
3.预编译的SQL：参数使用?作为占位符
4.步骤：
1.导入驱动jar包
2.注册驱动
3.获取数据库连接对象Connection
4.定义SQL
*注意：SQL的参数使用?作为占位符。如：select * from admin where username = ? and password = ?;
5.获取执行SQL语句的对象PreparedStatement Connection.preparedStatement(String sql)
6.给?赋值：
*方法：setXxx(参数1，参数2)
*参数1：?的位置编号，从1开始
*参数2：?的值
7.执行SQL，接收返回结果
8.处理结果
9.释放资源
package JDBC;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import util.JDBCUtils;
public class JudgeLogin {
public static void main(String[] args) {
try (Scanner scanner = new Scanner(System.in)) {
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
boolean result = new JudgeLogin().login(username, password);
if(result == true) {
System.out.println("登陆成功");
}else {
System.out.println("用户名或密码错误");
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
public boolean login(String username,String password) {
if(username == null || password == null) {
return false;
}
try {
connection = JDBCUtils.getConnection();
String sql = "select * from admin where username = ? and password = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(connection, preparedStatement, resultSet);
}
return false;
}
}
##JDBC控制事务：
1.事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。
2.操作：
1.开启事务
2.提交事务
3.回滚事务
3.使用Connection对象来管理事务
*开启事务：setAutoCommit(boolean autoCommit):调用该方法设置参数为false，即开启事务
*在执行SQL之前开启事务
*提交事务：commit()
*当所有SQL都执行完提交事务
*回滚事务：rollback()
*在catch中回滚事务
package JDBC;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import util.JDBCUtils;
public class Commit {
public static void main(String[] args) {
try (Scanner scanner = new Scanner(System.in)) {
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
boolean result = new Commit().login(username, password);
if(result == true) {
System.out.println("登陆成功");
}else {
System.out.println("用户名或密码错误");
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
public boolean login(String username,String password) {
if(username == null || password == null) {
return false;
}
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql = "select * from admin where username = ? and password = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
connection.commit();
return resultSet.next();
} catch (Exception e) {
try {
if(connection!=null) {
connection.rollback();
}
} catch (Exception e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.close(connection, preparedStatement, resultSet);
}
return false;
}
}
2282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
