一,概述
概念:Java Database Connectivity,Java数据库连接,用java语言操作数据库。
本质:官方(Sun)公司定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这一套接口,提供数据库驱动jar包,我们可使用这套JDBC接口编程,真正执行的代码是jar包中实现的类。
使用步骤:
1,导入驱动jar包;
2,注册驱动;
3,获取数据库连接对象Connection;
4,获取执行sql语句的对象Statement;
5,定义SQL语句;
6,执行SQL,接收返回结果;
7,处理结果;
8,释放资源。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) throws Exception {
//1,注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2,获取数据库连接对象
// Connection conn = DriverManager.getConnection(
// "jdbc:mysql://localhost:3306/onlineedu?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC",
// "root", "123456");
Connection conn = DriverManager.getConnection(
"jdbc:mysql:///onlineedu?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC",
"root", "123456");
//获取执行sql语句的对象
Statement stt = conn.createStatement();
String sql = "update operations_userlove set love_type = 3 where id = 5";
int i = stt.executeUpdate(sql);
System.out.println(i);
stt.close();
conn.close();
}
}
二,JDBC中各个对象
1,DriverManager:驱动管理对象
功能:
(1)注册驱动:告诉程序该使用哪一个数据库驱动jar包。
static void registerDriver(Driver driver):注册给定的驱动程序DriverManager.
写代码时使用:
Class.forName("com.mysql.cj.jdbc.Driver");
原因是:在com.mysql.cj.jdbc.Driver类源代码,存在以下静态代码块
static {
try{
java.sql.DriverManager.registerDriver(new Driver);
}catch(SQLException E){
throw new RunTimeException("Can't register Driver");
}
}
注意:mysql5之后的驱动jar包可省略注册驱动的步骤。
(2)获取数据库连接
方法:
static Connection getConnection(String url,String user,String password)
参数:
url:指定连接的路径
语法:jdbc:mysql://ip域名:端口号/数据库名称?参数
细节:若连接的是本机mysql服务器,且端口为3306,则url可写为:jdbc:mysql:///数据库名?参数
示例:Connection conn = DriverManager.getConnection(
"jdbc:mysql:///onlineedu?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC",
"root",
"123456");
user:用户名
password:密码
2,Connection:数据库连接对象
(1)获取执行sql的对象
Statement createStatement()
PreparedStatement PreparedStatement (String sql)
(2)管理事务
开启事务:setAutoCommit(boolean autoCommit),参数设为false,即开启事务。
提交事务:commit()
回滚事务:rollback()
3,Statement:执行sql对象
boolean execute(String sql):可执行任意sql,了解即可。
int executeUpdate(String sql):执行DML(insert、update、delete)语句,返回值是表记录中受影响的行数;DDL(对表和库进行create alter drop)语句,返回值是0。
ResultSet executeQUery(String sql):执行DQL(select)语句,返回查询的结果集。处理结果的两种方法:
boolean next():游标向下移动一行,当前行没有数据则返回false。
Xxx getXxx(参数):参数可以为int(数据库表中的列标号,从1开始)、String(列名称),返回的是当前行的列数据。
使用步骤:
1,游标向下移动一行;
2,判断是否有数据;
3,获取数据。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo3 {
public static void main(String[] args) {
Connection conn = null;
Statement sttm = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///testdb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC",
"root", "123456");
sttm = conn.createStatement();
String sql = "update testdb set count = 1000 where name = '王五'";
String sql2 = "delete from testdb where id = 4";
int i = sttm.executeUpdate(sql2);
System.out.println(i);
if (i > 0){
System.out.println("执行成功!");
}else{
System.out.println("执行失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (sttm != null){
try {
sttm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
三,对查询结果进行读取和封装
示例:
public class Book {
private int id;
private String name;
private int price;
private Date pub_date;
private String author;
Setter and Getter,toString方法
}
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DemoSelect {
public static void main(String[] args) {
List<Book> list = new DemoSelect().findAll();
for (Book book : list) {
System.out.println(book);
}
}
public List<Book> findAll(){
Connection conn = null;
Statement sttm = null;
ResultSet res = null;
Book book = null;
List<Book> list = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///book?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC",
"root", "123456");
sttm = conn.createStatement();
String sql = "select * from app01_book";
res = sttm.executeQuery(sql);
System.out.println(res);
list = new ArrayList<Book>();
while (res.next()){
int id = res.getInt("id");
String name = res.getString("name");
int price = res.getInt("price");
Date pub_date = res.getDate("pub_date");
String author = res.getString("author");
book = new Book();
book.setId(id);
book.setName(name);
book.setPrice(price);
book.setPub_date(pub_date);
book.setAuthor(author);
list.add(book);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res != null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sttm != null){
try {
sttm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
}
四,JDBC工具类
工具类简化的代码逻辑:
1,采用静态代码块加载注册驱动;
2,用一个getConnection方法封装数据库数据库连接对象(采用配置文件设置访问的数据库库名,用户名及密码);
3,用一个close方法封装关闭数据库资源。
配置文件(等号右边没有引号!!!):
url= jdbc:mysql:///testdb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
user= root
password= 123456
driver= com.mysql.cj.jdbc.Driver
工具类代码:
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
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 {
Properties pro = new Properties();
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
//第一种加载JDBC.Properties的方法
URL res = classLoader.getResource("JDBC.Properties");
String path = res.getPath();
try {
pro.load(new FileReader(path));
} catch (IOException e) {
e.printStackTrace();
}
// 第二种加载JDBC.Properties的方法
// InputStream resourceAsStream = classLoader.getResourceAsStream("JDBC.Properties");
// try {
// pro.load(resourceAsStream);
// } catch (IOException e) {
// e.printStackTrace();
// }
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Connection conn = DriverManager.getConnection(JDBCUtils.url, JDBCUtils.user, JDBCUtils.password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Statement sttm,Connection conn){
if (sttm != null){
try {
sttm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void close(ResultSet res, Statement sttm, Connection conn){
if (res != null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
JDBCUtils.close(sttm,conn);
}
}
五、用户登录示例
import JDBCUtils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCDemo {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
boolean flag = new JDBCDemo().login(username, password);
if (flag){
System.out.println("登陆成功!");
}else{
System.out.println("登录失败!");
}
}
public boolean login(String username,String password){
if (username == null || password == null){
return false;
}
Connection conn = null;
Statement sttm = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
sttm = conn.createStatement();
String sql = "select * from user where username = '"+username+"'and password = '"+password+"'";
//System.out.println(sql);
resultSet = sttm.executeQuery(sql);
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet,sttm,conn);
}
return false;
}
}