JDBC
JDBC(Java Database Connectivity)
Java 数据库连接规范(一套接口)由 Sun 公司提供的
JDBC 核心类
JDBC 四个核心类
DriverManager 创建连接
Connection 连接类
Statement 执行 sql 语句
ResultSet 结果集
JDBC 连接步骤
1.注册驱动
2.获取连接 Connection
3.获取 sql 语句的执行对象 Statement
4.执行 sql 语句,返回结果集 ResultSet
5.处理结果集
6.关闭资源
注册驱动
DriverManager.registerDriver(new Driver));
Class.forName("com.mysql.jdbc.Driver");
获取连接对象
String url = "jdbc:mysql://localhost:3306/myjdbc";
连接方式一:
Connection connection = DriverManager.getConnection(url, "root", "123456");
连接方式二:
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456);
Connection connection = DriverManage.getConnection(url, info);
连接方式三:相当于使用了一个 get 请求,携带参数访问链接
String url = "jdbc:mysql:
Connection connection = DriverManager.getConnection(url);
// 获取执行 sql 语句的对象 Statement
Statement statement = connection.createStatement()
// 执行 sql 语句, 返回结果集
// 结果集中添加的索引要和查询语句中的字段对应
String sql = "select * from users"
ResultSet resultSet = statement.executeQuery(sql)
// 处理结果集
// 循环遍历结果集,输出结果
// 有记录 next() 方法返回 true, 没有返回 false
while(resultSet.next()) {
// 打印数据
// 注意:查询数据库时,索引从1开始
System.out.print(resultSet.getObject(1) + " ")
System.out.print(resultSet.getObject(2) + " ")
System.out.print(resultSet.getObject(3) + " ")
System.out.print(resultSet.getObject(4) + " ")
System.out.println(resultSet.getObject(5) + " ")
System.out.println("--------------------")
}
// 关闭资源
resultSet.close()
statement.close()
connection.close()
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/e4dfe6181bace714eee814bd83701409.png)
增删改查
@Test
public void testInsert() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456"
Connection connection = DriverManager.getConnection(url)
Statement statement = connection.createStatement()
String sql = "insert into users values(5, 'jianzhong', '123123', '123123@qq.com', '2018-03-22')"
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("插入成功")
}
statement.close()
connection.close()
}
@Test
public void testUpdate() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456"
Connection connection = DriverManager.getConnection(url)
Statement statement = connection.createStatement()
String sql = "update users set name='xuesheng'"
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("更新成功")
}
statement.close()
connection.close()
}
@Test
public void testDelete() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456"
Connection connection = DriverManager.getConnection(url)
Statement statement = connection.createStatement()
String sql = "delete from users where id=5"
int row = statement.executeUpdate(sql)
System.out.println(row)
if (row > 0) {
System.out.println("删除成功")
}
statement.close()
connection.close()
}
@Test
public void testSelect() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456"
Connection connection = DriverManager.getConnection(url)
Statement statement = connection.createStatement()
// 查询
String sql = "select id, name, email from users"
ResultSet resultSet = statement.executeQuery(sql)
// 处理结果集
while (resultSet.next()) {
// 可以直接填字段名称
System.out.print(resultSet.getObject("id") + " ")
System.out.print(resultSet.getObject("name") + " ")
System.out.println(resultSet.getObject("email") + " ")
}
resultSet.close()
statement.close()
connection.close()
}
连接数据库的异常处理
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class DemoException {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
ArrayList<User> arrayList = new ArrayList<>();
while(resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
arrayList.add(user);
}
for (User user : arrayList) {
System.out.println(user);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException("驱动加载失败");
} catch (SQLException e) {
throw new RuntimeException("获取连接失败");
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
connection = null;
}
}
}
}
将 JDBC 封装为一个工具类方法使用
创建 dbinfo.properties 文件,作为配置文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:
user=root
password=123456
JDBC 工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
Properties properties = new Properties();
FileInputStream fileInputStream;
try {
fileInputStream = new FileInputStream("src/dbinfo.properties");
properties.load(fileInputStream);
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
ResourceBundle resourceBundle = ResourceBundle.getBundle("dbinfo");
driverClass = resourceBundle.getString("driverClass");
url = resourceBundle.getString("url");
user = resourceBundle.getString("user");
password = resourceBundle.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException, ClassNotFoundException {
return DriverManager.getConnection(url, user, password);
}
public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
connection = null;
}
}
}
测试 JDBC 工具类是否成功
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.junit.Test;
public class TestJDBCUtil {
@Test
public void testSelect() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
ArrayList<User> arrayList = new ArrayList<>();
while(resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
arrayList.add(user);
}
for (User user : arrayList) {
System.out.println(user);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.closeAll(resultSet, statement, connection);
}
}
}
sql 语句注入问题
import java.util.Scanner;
public class Login {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号:");
String name = scanner.nextLine();
System.out.println("请输入密码:");
String password = scanner.nextLine();
DoLogin doLogin = new DoLogin();
User user = doLogin.findUser(name, password);
if (user != null) {
System.out.println(user);
} else {
System.out.println("登录失败");
}
scanner.close();
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DoLogin {
public User findUser(String name, String password) {
String sql = "select * from users where name = '" + name + "' and password = '" + password + "'";
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
User user = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
System.out.println(sql);
if (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.closeAll(resultSet, statement, connection);
}
return user;
}
}
sql 语句注入问题,添加恒成立的条件
产生的结果使输入 sql 的语句成为 select * from users;
而不是所想要的单人用户登录获取信息
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/a65abedc1ea0e2cab84629069eb7e51a.png)
解决方法
public User findUser(String name, String password) {
String sql = "select * from users where name = ? and password = ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
User user = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
System.out.println(sql);
if (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.closeAll(resultSet, preparedStatement, connection);
}
return user;
}
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/9a3e8e6f62434c99eba0f28da540572c.png)
只有在输入正确信息时,才能对应登录获取信息
http://blog.csdn.net/huzongnan/article/list