JDBC
JDBC
一，JDBC
数据的持久化
把数据永久的保存起来，主要的方式是存在硬盘上。
持久化的实现过程大部分是通过数据库来完成的
JDBC
1.数据库的驱动
导入外部驱动
需要引入mysql的驱动（jar包）
java.sql.Driver接口，所有驱动需要实现的接口
mysql： com.mysql.jdbc.Driver
8.0版本：com.mysql.cj.jdbc.Driver
oracle：oracle.jdbc.driver.OracleDriver
反射加载驱动，Class.forName("com.mysql.cj.jdbc.Driver")
2.URL地址
jdbc协议
jdbc:mysql://主机地址 : 端口号/数据库名
jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8
8.0版本还需要传参useSSL=false&serverTimezone=UTC
oracle:
jdbc:oracle:thin:@主机名称:oracle端口:数据库名
sqlServer:
jdbc:sqlServer://127.0.0.1端口号:DatabaseName=xxx
3.用户名
用户名：root
4.密码
密码：123
二，连接数据库
执行数据库的DML（增删改查）
java中3个接口定义了对数据库的调用
- Statement：用来执行静态sql语句并返回生成的对象
- PreparedStatement：预编译
- CallableStatement
import com.mysql.jdbc.Driver;
import org.testng.annotations.Test;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Objects;
public class TestJDBC {
public static void main(String[] args) throws ClassNotFoundException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException, SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123";
// 驱动的全类名
String driverName = "com.mysql.jdbc.Driver";
// 架载驱动类
Class clazz= Class.forName(driverName);
// 实例化Driver对象
Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
// 注册驱动
DriverManager.registerDriver(driver);
// 获取链接
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(Objects.nonNull(connection) ? "连接成功" : "连接失败");
}
// 简化版
@Test
public void test() throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123";
// 驱动的全类名
String driverName = "com.mysql.jdbc.Driver";
// 架载驱动类,也可以省略，因为spi机制会自动加载META-INF下的services下的java.sql.Driver文件
Class clazz= Class.forName(driverName);
// 获取链接
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(Objects.nonNull(connection) ? "连接成功" : "连接失败");
}
//优化版
@Test
public void test02() throws ClassNotFoundException, SQLException, IOException {
/*
使用属性文件的好处：
1、实现了代码和数据的分离，如果需要修改配置信息，直接在属性文件中修改即可，不需要深入代码
2、如果修改了配置信息，省去了编译的过程
*/
Properties properties = new Properties();
// 读取外部的properties属性文件
// 记住就好，复制粘贴就好
properties.load(TestJDBC.class.getClassLoader().getResourceAsStream("db.properties"));
String url = properties.getProperty("mysql.url");
String driverName = properties.getProperty("mysql.driverName");
String username = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
// // 1.加载驱动类
Class.forName(driverName);
// // 4.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection);
System.out.println(Objects.nonNull(connection) ? "数据库连接成功" : "数据库连接失败");
//测试
Statement stmt = null;
stmt = connection.createStatement();
// 2.执行sql语句
String sql = "INSERT INTO teacher (name) VALUES ('Jay')";
// 返回值是执行sql语句影响的行数
int i = stmt.executeUpdate(sql);
// 3.处理执行sql的返回值
System.out.println(i);
System.out.println("操作成功...");
connection.close();
stmt.close();
}
@Test
public void test01(){
// 1.获取连接
Connection conn = null;
Statement stmt = null;
try {
conn = GetConnection.test();
System.out.println(conn);
stmt = conn.createStatement();
// 2.执行sql语句
String sql = "INSERT INTO teacher (name) VALUES ('Jay')";
// 返回值是执行sql语句影响的行数
int i = stmt.executeUpdate(sql);
// 3.处理执行sql的返回值
System.out.println(i);
System.out.println("操作成功...");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//GetConnection.close(conn,stmt);
}
}
//查询
@Test
public void test03() throws SQLException {
// 1.获取连接
Connection conn = null;
Statement stmt = null;
ResultSet re = null;
try {
conn = GetConnection.test();
System.out.println(conn);
stmt = conn.createStatement();
// 2.执行sql语句
String sql = "select * from teacher";
//返回结果集合
re = stmt.executeQuery(sql);
// ResultSet封装了结果集的对象
while (re.next()){
int id = re.getInt("id");//索引从1开始
String name = re.getString("name");
System.out.println(id + " " + name);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
re.close();
stmt.close();
conn.close();
//GetConnection.close(conn,stmt);
}
}
}
db.properties文件（放在src根目录下）
mysql.url = jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8
mysql.username = root
mysql.password = 123
mysql.driverName = com.mysql.jdbc.Driver
GetConnection（连接数据库封装类）
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class GetConnection {
public static Connection test(){
Connection conn = null;
Properties properties = new Properties();
try {
properties.load(GetConnection.class.getClassLoader().getResourceAsStream("db.properties"));
String url = properties.getProperty("mysql.url");
String driverName = properties.getProperty("mysql.driverName");
String username = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
Class.forName(driverName);
conn = DriverManager.getConnection(url, username, password);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
return conn;
}
}
案例：连接数据库改变数据库
Teacher类
package jdbc;
public class Teacher {
private int id;
private String name;
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}TeacherDao类
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TeacherDao {
Connection conn = null;
Statement stmt = null;
{
conn = GetConnection.test();
try {
stmt = conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 增删改
public int update(String sql,Object ... args) throws SQLException {
int i = stmt.executeUpdate(sql);
conn.close();
stmt.close();
return i;
}
//查询所有
public List<Teacher> queryAll(String sql,Object ... args) throws SQLException {
List<Teacher> teachers = new ArrayList<>();
ResultSet re = stmt.executeQuery(sql);
while(re.next()){
int id = re.getInt("id");
String name = re.getString("name");
Teacher teacher = new Teacher(id,name);
teachers.add(teacher);
}
conn.close();
stmt.close();
return teachers;
}
}
实现类
import java.sql.SQLException;
public class TestTeacher {
private static TeacherDao teacherDao = new TeacherDao();
public static void main(String[] args) throws SQLException {
int update = teacherDao.update("delete from teacher where id =6 ");
}
}
浙公网安备 33010602011771号