JDBC 连接 MySQL 数据库的练习
JDBC 的 Statement 操作数据库 和 PreparedStatement 解决 sql 注入的练习
import org.junit.Test;
import java.sql.*;
import java.util.Scanner;
public class t3 {
@Test
public void login() {
try {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String pname = sc.next();
System.out.println("请输入密码");
String page = sc.next();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
Statement sta = conn.createStatement();
ResultSet rs = sta.executeQuery(
"select * from test1 where pname = '"+pname+"' "+" and page = '"+page+"' "
// 第一个 '"+pname+"' 第二个 "+" 第三个 '"+page+"'
);
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void login2() {
try {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String pname = sc.next();
System.out.println("请输入密码");
String page = sc.next();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"select * from test1 where pname =? and page =? "
// 没赋值 ?
);
ps.setString(1,pname);
ps.setString(2,page);
// 赋值 ?
ResultSet rs = ps.executeQuery(
);
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 修改
@Test
public void alter(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"update test1 set pname =? , page =? where pid=? "
// 没赋值 ?
);
ps.setString(1,"小李");
ps.setString(2,"25");
ps.setInt(3,2);
// 赋值 ?
int num = ps.executeUpdate();
System.out.println(num);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 增加
@Test
public void add(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"insert into test1 (pid,pname,page)values(?,?,?) "
// 没赋值 ?
);
ps.setInt(1,4);
ps.setString(2,"小陈");
ps.setString(3,"18");
// 赋值 ?
int num = ps.executeUpdate();
System.out.println(num);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 删除
@Test
public void drop(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"delete from test1 where pid=? "
// 没赋值 ?
);
ps.setInt(1,1);
int num = ps.executeUpdate();
System.out.println(num);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 模糊查询
@Test
public void like() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"select * from test1 where pname like ? "
// 没赋值 ?
);
ps.setString(1,"%"+"杰"+"%");
// 赋值 ?
ResultSet rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("pid");
String pname = rs.getString("pname");
String page = rs.getString("page");
System.out.println(id+"\t"+pname+"\t"+page);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 分页查询
@Test
public void limit() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
PreparedStatement ps = conn.prepareStatement(
"select * from test1 limit ?,? "
// 没赋值 ?
);
ps.setInt(1,0);
ps.setInt(2,3);
// 赋值 ?
ResultSet rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("pid");
String pname = rs.getString("pname");
String page = rs.getString("page");
System.out.println(id+"\t"+pname+"\t"+page);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void test(){
Scanner sc = new Scanner(System.in);
System.out.println("请输入");
String sb = sc.next();
System.out.println(sb);
}
@Test
public void upda(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
Statement sta = conn.createStatement();
// int rs = sta.executeUpdate("insert into test1 (pid,pname)values('3','小杰')");
int rs = sta.executeUpdate("update test1 set page='21' where pid='3'");
if(rs>0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void select(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8","root","root");
Statement sta = conn.createStatement();
ResultSet rs = sta.executeQuery("select * from test1");
while (rs.next()){
int id = rs.getInt("pid");
String pname = rs.getString("pname");
System.out.println(id+"\t"+pname);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
数据库表
update 工具类 和 Query工具类
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class basicDao {
public Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day08?characterEncoding=utf-8", "root", "root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
// update工具类
public int update(String sql, Object[] objects) {
int num = -1;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
if (objects != null && objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
ps.setObject((i + 1), objects[i]);
}
}
num = ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return num;
}
// Query工具类
public List<User1> selectAll(String sql, Object[] objects) {
List<User1> list = new ArrayList<User1>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = getConnection();
ps = con.prepareStatement(sql);
if (objects != null && objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
ps.setObject((i + 1), objects[i]);
}
}
rs = ps.executeQuery();
while (rs.next()) {
// int id = rs.getInt("pid");
// String pname = rs.getString("pname");
// String page = rs.getString("page");
// User user = new User(id, pname, page);
int id = rs.getInt("id");
String pname = rs.getString("name");
String health = rs.getString("health");
String love = rs.getString("love");
String strain = rs.getString("strain");
User1 user1 = new User1(id, pname, health,love,strain);
list.add(user1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return list;
}
}
User1 实体类
import java.io.Serializable;
public class User1 implements Serializable{
public User1() {
}
public User1(int id, String name, String health, String love, String strain) {
this.id = id;
this.name = name;
this.health = health;
this.love = love;
this.strain = strain;
}
private int id;
private String name;
private String health;
private String love;
private String strain;
@Override
public String toString() {
return "User1{" +
"id=" + id +
", name='" + name + '\'' +
", health='" + health + '\'' +
", love='" + love + '\'' +
", strain='" + strain + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHealth() {
return health;
}
public void setHealth(String health) {
this.health = health;
}
public String getLove() {
return love;
}
public void setLove(String love) {
this.love = love;
}
public String getStrain() {
return strain;
}
public void setStrain(String strain) {
this.strain = strain;
}
}
测试类
import java.util.List;
public class hw1 {
public static void main(String[] args) {
basicDao dao = new basicDao();
// String sql = "insert into te2 (id,name,health,love,stain)values(?,?,?,?,?)";
// Object[] objects = {3,"小花",10,10,"big" };
// int num = dao.update(sql,objects);
// System.out.println(num);
// String sql2 = "update te2 set name=? where id=?";
// Object[] objects2= {"小霞",1};
// int num2 = dao.update(sql2,objects2);
// System.out.println(num2);
// String sql3 ="select * from te2 limit ?,?";
// Object[] objects3={0,3};
// List<User1> list = dao.selectAll(sql3,objects3);
// for (User1 user1 :list){
// System.out.println(user1);
String sql3 ="select * from te2 where name like ?";
Object[] objects3={"%" +"花" +"%"};
List<User1> list = dao.selectAll(sql3,objects3);
for (User1 user1 :list){
System.out.println(user1);
}
}
}
数据库表
结果展示：
