JDBC
JDBC(Java Database Connectivity)
Java 数据库连接规范(一套接口)由 Sun 公司提供的
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);
Statement statement = connection.createStatement();
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()) {
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));
}
resultSet.close();
statement.close();
connection.close();
测试增删改查
@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(4, 'zs', '123', '123@163.com', '2018-01-01
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='zs"
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()
}
连接数据库时的异常处理
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;
}
}
}
创建 dbinfo.properties 文件,作为配置文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=123456
将 JDBC 封装为一个工具类方法使用
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 工具类
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 语句注入问题
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();
}
}
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;
解决方法
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;
}