JDBC连接及操作数据库
连接数据库步骤
- 注册驱动
- 获取连接
- 获取statement对象
- 执行SQL语句返回结果集
- 遍历结果集
- 关闭连接释放资源
传统方式连接数据库
public class JDBCDemo {
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
//1.注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm", "root", "root");
//3.获取Statement对象
PreparedStatement preparedStatement = connection.prepareStatement("select * from tb_user");
//4.执行SQL语句返回结果集
ResultSet resultSet = preparedStatement.executeQuery();
//5.遍历结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("username"));
}
//6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
缺点
注册驱动时,当前类和MySQL的驱动类有很强的依赖关系。当我们没有驱动类的时候,连编译都不能通过。这种调用者与被调用者之间的依赖关系,就叫做程序的耦合,耦合分为高耦合(紧密联系)和低耦合(松散联系)
我们在开发中,理想的状态应该是编译时不依赖,运行时才依赖。
要做到编译时不依赖,就需要使用反射来创建类对象。
编译时不依赖的数据库连接
public class JDBCDemo {
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
//1.注册驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm", "root", "root");
//3.获取Statement对象
PreparedStatement preparedStatement = connection.prepareStatement("select * from tb_user");
//4.执行SQL语句返回结果集
ResultSet resultSet = preparedStatement.executeQuery();
//5.遍历结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("username"));
}
//6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
优点
我们的类中不再依赖具体的驱动类,此时就算删除mysql的驱动jar包依然可以通过编译,只不过因为没有驱动类所以不能运行罢了。
缺点
我们反射类对象的全限定类名称是在java类中写死的,数据库的端口号、用户名密码也是写死的,一旦要修改就等于是要修改源码。
自己小打小闹写的代码改源码什么的还好说,但如果是上线项目,改源码势必要停服务器重新编运行。
数据库操作
案例1
import java.sql.*;
/**
* JDBC java database connectivity java 数据库连接
* JDBC是由SUM公司提供的一套API,是使用java连接数据的一套API接口,
* 各个数据库提供上都实现了这一条接口
* 提供了连接其提供的数据库产品的实现类(以jar包形式,又称为连接该数据库的驱动包)。
*/
public class JDBCDemo01 {
private static final String url = "jdbc:mysql://localhost:3306/dntest?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
private static final String user = "root";
private static final String password = "362522";
private static Connection connection = null;
private static Statement statement = null;
private static ResultSet rs = null;
public static void createdata() {
/**
* 使用JDBC连接数据库的步骤
* 1.加载驱动 CLass.forName()
* 2.建立连接 DriverManager.getConnection()
* 3.获取执行对象
*/
try {
Class.forName("com.mysql.cj.jdbc.Driver");
/**
* getConnection(String url,String user, String password)
*/
connection = DriverManager.getConnection(url, user, password);
/**
* 通过连接对象Connection获取执行SQL的执行对象Statement
* Statement对象用于向数据库执行SQL语句
*/
statement = connection.createStatement();
String sql = "INSERT INTO userinfo (user_name,password,pet_name,age) VALUES ('user','000000','用户',24)";
// boolean execute = statement.execute(sql);
// if (!execute) {
// System.out.println("添加成功");
// } else {
// System.out.println("添加失败");
// }
int sum = statement.executeUpdate(sql);
if (sum > 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void readdata() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
String sql = "SELECT * FROM userinfo";
rs = statement.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("user_name");
String pw = rs.getString("password");
String petname = rs.getString("pet_name");
int age = rs.getInt("age");
System.out.println(id + " " + name + " " + pw + " " + petname + " " + age);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void updatedata() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
String sql = "UPDATE userinfo SET password = '000000' WHERE user_name = 'user'";
int sum = statement.executeUpdate(sql);
if (sum > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void deletedata() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
String sql = "DELETE FROM userinfo WHERE id = 4";
int sum = statement.executeUpdate(sql);
if (sum > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
//CRUD(create, read, update, delete)
updatedata();
}
}
案例2
import java.sql.*;
public class Test {
private static final String url = "jdbc:mysql://localhost:3306/dntest?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
private static final String user = "root";
private static final String password = "362522";
private static Connection connection = null;
private static Statement statement = null;
private static ResultSet rs = null;
public static void main(String[] args) {
readStudent();
}
private static void createStudent() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
String sql;
int sum = 0, sun = 0;
for (int i = 0; i < 50; i++) {
String stuname = CreateData.createName();
int age = CreateData.createAge();
int classid = CreateData.createClassId();
sql = "INSERT INTO student (name,age,class_id) VALUES ('" + stuname + "'," + age + "," + classid + ")";
sum = statement.executeUpdate(sql);
if (sum > 0) {
sun += 1;
}
}
if (sun == 50) {
System.out.println("添加成功");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void readStudent() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
String sql = "SELECT s.id,s.name,s.age,s.class_id,c.name class_name " +
"FROM student s,class c " +
"WHERE s.class_id = c.id;";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getInt("id") + " ");
System.out.print(rs.getString("name") + " ");
System.out.print(rs.getInt("age") + " ");
System.out.print(rs.getInt("class_id") + " ");
System.out.println(rs.getString("class_name"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DBUtil
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 数据库工具类
*/
public class DBUtil {
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取一个数据库连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
final String url = "jdbc:mysql://localhost:3306/dntest?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
final String user = "root";
final String password = "362522";
return DriverManager.getConnection(url, user, password);
}
}
案例3
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCDemo03 {
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
public static void main(String[] args) {
try (
Connection connection = DBUtil.getConnection();
) {
/**
* 预编译SQL语句是将在SQL中会编译的值(原来拼接SQL语句的部分)先以“?”进行占位
*/
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的名字:");
String name = scanner.nextLine();
String sql = "SELECT * FROM user WHERE username = ?";
pstmt = connection.prepareStatement(sql);
//name字段是varchar,因此这里设置该?对应的值应当选取字符串类型
pstmt.setString(1, name); //设置第一个?值为name
rs = pstmt.executeQuery();
if (rs.next()) {
String username = rs.getString(1);
String password = rs.getString(2);
System.out.println(username + " " + password);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
使用配置文件连接数据库
配置文件
创建db.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/javaweb
jdbc.username=root
jdbc.password=362522
创建JDBCDemo.java文件
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class JDBCDemo {
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
//读取配置文件db.properties
Properties prop = new Properties();
prop.load(new FileInputStream("src\\main\\resources\\db.properties"));
//获取配置文件中的相关参数值
String driver = prop.getProperty("jdbc.driver");
String url = prop.getProperty("jdbc.url");
String user = prop.getProperty("jdbc.username");
String password = prop.getProperty("jdbc.password");
//1.注册驱动
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection(url,user,password);
//3.获取Statement对象
PreparedStatement preparedStatement = connection.prepareStatement("select * from user");
//4.执行SQL语句返回结果集
ResultSet resultSet = preparedStatement.executeQuery();
//5.遍历结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("username"));
}
//6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}