JDBC,用java来操作数据库增删改查
建表代码
这里是建表需要的代码,这里规定一个表,初始是这样的
--建表的代码
CREATE TABLE ceshi(
id INT PRIMARY KEY AUTO_INCREMENT,
people VARCHAR(50) NOT NULL UNIQUE,
age INT NOT NULL
);
-- 插入的四条数据
INSERT INTO ceshi(people,age) VALUES("张珊",18);
INSERT INTO ceshi(people,age) VALUES("李思",20);
INSERT INTO ceshi(people,age) VALUES("王无",19);
INSERT INTO ceshi(people,age) VALUES("周九",21);
用户类
接下来这个是建立了一个数据表的类,用于后续构建对象使用。
public class cePeople {
//主键id,基本数据类型使用对应好的包装类型
private Integer id;
//名字
private String Pname;
//年龄
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getPname() {
return Pname;
}
public void setPname(String pname) {
Pname = pname;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "people{" +
"id=" + id +
", Pname='" + Pname + '\'' +
", age=" + age +
'}';
}
}
核心代码
PreparedStatement后面有讲是干什么的
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class CeTest {
// 查询表中所有数据到集合中显示出来
@Test
public void selectTest() throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
String username = "你的账户名称";
String password = "你的账户密码";
Connection conn = DriverManager.getConnection(url, username, password);
//4.定义sql
String sql = "select * from ceshi";
//5.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//6.设置参数
//7.执行sql
ResultSet rs = pstmt.executeQuery();
//8.处理结果
List<cePeople> peList = new ArrayList<>();
while (rs.next()) {
//1.获取数据。
int id = rs.getInt("id");
String Pname = rs.getString("people");
int age = rs.getInt("age");
//2.封装对象。
cePeople peop = new cePeople();
peop.setId(id);
peop.setPname(Pname);
peop.setAge(age);
//3.集合处理
peList.add(peop);
}
//System.out.println(peList);
for (int i = 0; i < peList.size(); i++) {//循环遍历1.0
cePeople c1 = peList.get(i);//得到下标的每一个元素
System.out.println(c1.getId() + "," + c1.getPname() + "," + c1.getAge());
}
//9.关闭资源
rs.close();
pstmt.close();
conn.close();
}
// 添加数据
@Test
public void addTest() throws ClassNotFoundException, SQLException {
//添加数据
String Pname = "麻子";
int age = 21;
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
String username = "你的账户名称";
String password = "你的账户密码";
Connection conn = DriverManager.getConnection(url, username, password);
//4.定义sql
String sql = "insert into ceshi(people,age) values(?,?)";
//5.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//6.设置参数
pstmt.setString(1, Pname);
pstmt.setInt(2, age);
//7.执行sql
int count = pstmt.executeUpdate();//得到影响行数
//8.处理结果
System.out.println(count > 0);
//9.关闭资源
pstmt.close();
conn.close();
}
// 删除数据,根据id
@Test
public void deleteTest() throws ClassNotFoundException, SQLException{
//删除id是5的数据
int id=5;
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
String username = "你的账户名称";
String password = "你的账户密码";
Connection conn = DriverManager.getConnection(url, username, password);
//4.定义sql
String sql = "delete from ceshi where id=?";
//5.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//6.设置参数
pstmt.setInt(1, id);
//7.执行sql
int count = pstmt.executeUpdate();//得到影响行数
//8.处理结果
System.out.println(count > 0);
//9.关闭资源
pstmt.close();
conn.close();
}
// 修改数据,根据名字
@Test
public void alterTest() throws ClassNotFoundException, SQLException{
//修改的数据
int id=5;
int age = 18;
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
String username = "你的账户名称";
String password = "你的账户密码";
Connection conn = DriverManager.getConnection(url, username, password);
//4.定义sql
String sql = "update ceshi set age=? where id=?";
//5.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//6.设置参数
pstmt.setInt(1,age);
pstmt.setInt(2, id);
//7.执行sql
int count = pstmt.executeUpdate();//得到影响行数
//8.处理结果
System.out.println(count > 0);
//9.关闭资源
pstmt.close();
conn.close();
}
}
查询输出的数据
添加好的一条数据
tips:定义sql语句,先写单引号,在添双引号的内容。
String name="xxx";
String pwd="223";
String sql="select * from user_tb where username= '"+name+"' and password='"+pwd+"'";
PreparedStatement函数
下面是上次JDBC中的API没有讲解完,是防止sql注入的函数。
prepareStatement对象防止sql注入的方式是把用户非法输入的单引号用\反斜杠做了转义,从而达到了防止sql注入的目的。
下面是样例,这样可以防止pwd="'or ‘1’='1"这样的sql数字型注入
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
这里记得添加预编译功能,因为他默认是关闭的,你要自己打开。
//定义变量
String name="qww";
String pwd="223";
String pwd2="'or '1'='1";这个是sql注入简单版的。
//定义sql
String sql="select * from user_tb where username= ? and password=?";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置问号的值,相当于进行一个转义,让特殊字符无法注入数据库
pstmt.setString(1,name);
pstmt.setString(2,pwd);
//执行sql
ResultSet rs=pstmt.executeQuery();
//判断是否成功
if(rs.next()){
System.out.println("成功登录");
}else{
System.out.println("失败了");
}
// 释放资源
rs.close();
pstmt.close();
conn.close();
:|