JDBC
JDBC(Java Database Connectivity) Java连接数据库的规范(标准),可以使用Java语言连接数据库完成CRUD操作
一:JDBC入门程序
- 1.获取MySQL驱动包
MySQL驱动包下载
- 2.导入 驱动包
- 3.编写程序
package com.qf;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo {
//第一种连接方式 【不推荐,硬编码,如果切换数据库必须改代码】
public static void main(String[] args) throws Exception {
//1:实例化驱动类
Driver driver = new Driver();
//2:注册驱动
DriverManager.registerDriver(driver);
//3:创建连接对象
String url = "jdbc:mysql://localhost:3306/companydb?characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection);
}
//连接数据库的第二种方式
public static void main(String[] args) throws Exception {
//通过反射机制去加载com.mysql.jdbc.Driver类
Class<?> clazzInfo = Class.forName("com.mysql.jdbc.Driver");
//通过类对象【类的对象】
Driver driver = (Driver) clazzInfo.newInstance();
//注册驱动
DriverManager.registerDriver(driver);
//获取连接
String url = "jdbc:mysql://localhost:3306/companydb?characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection);
}
//第三种方式
public static void main(String[] args) throws Exception {
//通过反射机制去加载com.mysql.jdbc.Driver类
//类加载时会立即执行静态代码块
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://localhost:3306/companydb?characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection.isClosed());
//获取statement对象
Statement statement = connection.createStatement();
//executeUpdate实现数据的增删改executeQuery负责查询
String sql = "insert into account values(7,'张三',1000)";
//影响的行数
int i = statement.executeUpdate(sql);
if(i == 1){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
statement.close();
connection.close();
}
}
- JDBC实现查询
public static void main(String[] args) throws Exception {
//通过反射机制去加载com.mysql.jdbc.Driver类
//类加载时会立即执行静态代码块
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://localhost:3306/companydb?characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println(connection.isClosed());
//获取statement对象
Statement statement = connection.createStatement();
String sql = "select * from account";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int balance = resultSet.getInt("balance");
System.out.println("id=="+id+"==name"+name+"==balance=="+balance);
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
}
二:登录案例
创建数据库
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL
)
实现登陆功能
/**
* 实现登录功能
* @param args
*/
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
//输入用户名
System.out.println("请输入用户名");
String username = scanner.next();
//输入密码
System.out.println("请输入密码");
String password = scanner.next();
//获取数据库连接
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///companydb?characterEncoding=utf8&useSSL=false",
"root", "123456");
//sql
String sql = "select * from t_user where username='"+username+"' and password = '"+password+"'";
System.out.println(sql);
//执行sql
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
resultSet.close();
statement.close();
connection.close();
}
问题分析
sql注入漏洞
解决问题
使用PreparedStatement对sql进行预编译,来防止sql注入漏洞,只能用PreparedStatement不要用Statement
PreparedStatement相对于Statement的优点:
1:防止sql注入漏洞
2:提高查询效率
3:简化sql语句,不需要字符串拼接
//通过预编译解决sql注入的漏洞
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
//输入用户名
System.out.println("请输入用户名");
String username = scanner.next();
//输入密码
System.out.println("请输入密码");
String password = scanner.next();
//获取数据库连接
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///companydb?characterEncoding=utf8&useSSL=false",
"root", "123456");
//sql
String sql = "select * from t_user where username=? and password=?";
System.out.println(sql);
//预编译sql
PreparedStatement ps = connection.prepareStatement(sql);
//给?赋值
ps.setString(1,username);
ps.setString(2,password);
//执行sql
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
resultSet.close();
ps.close();
connection.close();
}
三:分层开发
3.1 为什么要分层?
3.2 三层简介
- UI (user interface)层 (控制器层):用户交互层,接受用户输入的参数,校验用户的参数
- Service层(业务逻辑层):业务逻辑开发,业务逻辑判断
- Dao(data access object)(数据持久层):负责与数据库的交互
3.3 实现三层开发
使用三层开发实现登陆功能
Dao层代码
package com.qf.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
/**
* 数据持久层
* 负责数据库的交互(insert、delete、update、select)
*/
public class UserDao {
/**
* 根据用户名和密码查询用户的信息
* @param username 用户名
* @param password 密码
* @return map
* @throws Exception
*/
public Map findUserByUsernameAndPassword(String username,String password)throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///companydb?characterEncoding=utf8&useSSL=false",
"root", "123456");
//sql
String sql = "select * from t_user where username=? and password=?";
System.out.println(sql);
//预编译sql
PreparedStatement ps = connection.prepareStatement(sql);
//给?赋值
ps.setString(1,username);
ps.setString(2,password);
//执行sql
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
//用户信息封装到map集合
Map user = new HashMap();
user.put("id",resultSet.getInt("id"));
user.put("username",resultSet.getString("username"));
user.put("password",resultSet.getString("password"));
return user;
}else{
return null;
}
}
}
Service层代码
package com.qf.service;
import com.qf.dao.UserDao;
import java.util.Map;
public class LoginService {
private UserDao userDao = new UserDao();
public void login(String username,String password){
//todo: 查询数据库,获取用户信息
try {
Map user = userDao.findUserByUsernameAndPassword(username, password);
//登录业务
if(user == null){
System.out.println("登录失败");
}else{
System.out.println("登录成功");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("登录失败");
}
}
}
UI层代码
package com.qf.ui;
import com.qf.service.LoginService;
import java.util.Scanner;
/**
* ui层
* 1:负责接受参数
* 2:负责校验参数
*/
public class UI {
private static LoginService loginService = new LoginService();
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
//输入用户名
System.out.println("请输入用户名");
String username = scanner.next();
//输入密码
System.out.println("请输入密码");
String password = scanner.next();
if("".equals(username)){
System.out.println("用户名必须填写");
return;
}
if("".equals(password)){
System.out.println("密码必须填写");
return;
}
//todo: 登录业务
loginService.login(username,password);
}
}