第12步---MySQL的JDBC操作
1.概述
采用Java API 的方式实现数据之间的操作。
根据不同的数据库采用了不同的驱动，接口是一致的。
下载的地址
MySQL :: Download MySQL Connector/J (Archived Versions)
2.执行流程
注册驱动
创建连接
执行sql语句的对象
结果集：虚拟表
3.入门案例
创建数据
DROP TABLE IF EXISTS student;
create table if not exists student(
sid int primary key auto_increment,
sname varchar(20),
age int
);
insert into student values(NULL,'宋江',30),(NULL,'武松',28),(NULL,'林冲',26);
select * from student;编写Java代码读取数据
public class Test {
public static void main(String[] args) throws SQLException {
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
,"root","root");
//执行sql
Statement statement = connection.createStatement();
//执行sql
ResultSet resultSet = statement.executeQuery("select * from student");
//遍历结果集
while (resultSet.next()) {
//循环获取每一行数据
Integer sid= resultSet.getInt("sid");
String sname= resultSet.getString("sname");
Integer age= resultSet.getInt("age");
System.out.println(sid+"-"+sname+"-"+age);
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
4.结果集优化
public class Test {
public static void main(String[] args) throws SQLException {
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "root");
//执行sql
Statement statement = connection.createStatement();
//执行sql
ResultSet resultSet = statement.executeQuery("select * from student");
ResultSetMetaData metaData = resultSet.getMetaData();
//表得列数
int line = metaData.getColumnCount();
//遍历结果集
while (resultSet.next()) {
for (int i = 1; i <= line; i++) {
//获取每一列得数据
System.out.print(resultSet.getObject(i) + "\t");
}
System.out.println();
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
此时打印数据得时候就不用局限于自己设置对应得属性得名称了。
5.增删改操作
插入数据
public class Test2 {
public static void main(String[] args) throws SQLException {
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "root");
//执行sql
Statement statement = connection.createStatement();
//执行sql
statement.executeUpdate("insert into student(sid,sname,age ) values (4,'王公公',20)");
//关闭连接
statement.close();
connection.close();
}
}插入数据的时候默认影响的插入的行数的信息
public class Test2 {
public static void main(String[] args) throws SQLException {
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "root");
//执行sql
Statement statement = connection.createStatement();
//执行sql
int line=statement.executeUpdate("insert into student(sid,sname,age ) values (5,'王公公',20)");
System.out.println("影响的行数："+line);
//关闭连接
statement.close();
connection.close();
}
}
修改数据和删除数据就是把执行的sql换一下就可以了，换成我们需要设置的sql就可以。
//删除数据
int line=statement.executeUpdate("delete from student where sid=5");
System.out.println("影响的行数："+line);
6.SQL注入问题
用户输入的内容修改了sql本身的内容就是sql注入的问题。
创建测试的表数据
drop table if exists user;
create table user(
uid int primary key auto_increment,
username varchar(20),
password varchar(20)
);
insert into user values(NULL, 'zhangsan','123456'),(NULL,'lisi','888888');
模拟用户登录的操作
public class Test3 {
public static void main(String[] args) throws SQLException {
//模拟用户登录操作
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名“：");
String username=scanner.nextLine();
System.out.println("请输入密码：");
String password=scanner.nextLine();
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "root");
//执行sql
Statement statement = connection.createStatement();
//执行sql
String sql="select * from user where username = '" +username +"' and password = '"+password+"'";
System.out.println("sql："+sql);
ResultSet resultSet = statement.executeQuery(sql);
ResultSetMetaData metaData = resultSet.getMetaData();
//表得列数
int line = metaData.getColumnCount();
//遍历结果集
if (resultSet.next()) {
System.out.println("登录成功！");
}
else {
System.out.println("登录失败！");
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
上面都是正常的操作
下面是sql注入的内容，改变了原先的sql的内容了
解决sql注入的问题是采用preparedStatement的方式采用参数的方式填入用户输入的数据。
public class Test4 {
public static void main(String[] args) throws SQLException {
//模拟用户登录操作
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名“：");
String username=scanner.nextLine();
System.out.println("请输入密码：");
String password=scanner.nextLine();
//注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/pz?serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "root");
//执行sql
String sql="select * from user where username = ? and password = ? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//填充参数
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
System.out.println("sql："+sql);
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
//表得列数
int line = metaData.getColumnCount();
//遍历结果集
if (resultSet.next()) {
System.out.println("登录成功！");
}
else {
System.out.println("登录失败！");
}
//关闭连接
resultSet.close();
preparedStatement.close();
connection.close();
}
}
此时就解决了sql注入的问题。
