JDBC之数据操作
JDBC之增删改(update)
JDBC对数据的操作中，增、删、改操作都是使用一个方法，因
案例
package com.lyc.lesson;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class Demo01 {
public static void main(String[] args) throws Exception {
//添加
java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
add(1, "李四", 5900, date);
//删除
int i = deleteById(1);
if(i>0){
System.out.println("删除成功！");
} else{
System.out.println("删除失败");
}
//修改
updateById(7369, "张三");
}
//添加用户
public static boolean add(int empno, String ename, double sal, Date hiredate){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "******");
sta = conn.createStatement();
i = sta.executeUpdate("insert into emp(empno,ename,sal,hiredate) values (" + empno + ",'" + ename + "'," + sal + ",'" + hiredate + "')");
} catch (Exception e) {
e.printStackTrace();
}
try {
sta.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if( i >0){
return true;
} else{
return false;
}
}
//删除用户
public static int deleteById(int id){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "*******");
sta= conn.createStatement();
i = sta.executeUpdate("delete from emp where empno = " + id + "");
} catch (Exception e) {
e.printStackTrace();
}
try {
sta.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
//修改用户信息
public static boolean updateById(int empno,String ename){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "******");
sta = conn.createStatement();
i = sta.executeUpdate("update emp set ename = '" + ename + "' where empno = " + empno + "");
} catch (Exception e) {
e.printStackTrace();
}
if( i > 0){
return true;
} else{
return false;
}
}
}
JDBC之查询(Query)
按照下标取值
package com.lyc.lesson;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class Demo01 {
public static void main(String[] args) throws Exception {
//添加
java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
boolean ad = add(1, "李四", 5900, date);
if( ad){
System.out.println("添加成功");
} else{
System.out.println("添加失败");
}
//删除
int i = deleteById(1);
if(i>0){
System.out.println("删除成功！");
} else{
System.out.println("删除失败");
}
//修改
boolean ud = updateById(7369, "张三");
if(ud){
System.out.println("修改成功");
} else{
System.out.println("修改失败");
}
}
//添加用户
public static boolean add(int empno, String ename, double sal, Date hiredate){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "*******");
sta = conn.createStatement();
i = sta.executeUpdate("insert into emp(empno,ename,sal,hiredate) values (" + empno + ",'" + ename + "'," + sal + ",'" + hiredate + "')");
} catch (Exception e) {
e.printStackTrace();
}
try {
sta.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if( i >0){
return true;
} else{
return false;
}
}
//删除用户
public static int deleteById(int id){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "******");
sta= conn.createStatement();
i = sta.executeUpdate("delete from emp where empno = " + id + "");
} catch (Exception e) {
e.printStackTrace();
}
try {
sta.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
//修改用户信息
public static boolean updateById(int empno,String ename){
Connection conn = null;
Statement sta = null;
int i = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "******");
sta = conn.createStatement();
i = sta.executeUpdate("update emp set ename = '" + ename + "' where empno = " + empno + "");
} catch (Exception e) {
e.printStackTrace();
}
if( i > 0){
return true;
} else{
return false;
}
}
}
按照列名取值
package com.qf.jdbc;
import java.sql.*;
public class Demo1_select {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false","root","******");
Statement s = conn.createStatement( );
// 执行查询使用executeQuery
String sql = "select sal,hiredate,empno id,ename name from emp";
// 查询返回的是一种虚拟表,用集合装数据.即ResultSet
ResultSet rs = s.executeQuery(sql);
while(rs.next()){
// 列名取值
int empno = rs.getInt("id");
String ename = rs.getString("name");
double sal = rs.getDouble("sal");
Date hiredate = rs.getDate("hiredate");
System.out.println(empno+"-"+ename+"-"+sal+"-"+hiredate);
}
// 结果集关流
rs.close();
s.close();
conn.close();
}
}
以上的两种查询方法，我们一般使用第二种。
对于按照下标取值来讲，下标和通过sql得到的虚拟表中的列名是一一对应的关系，一旦下标的顺序发生错乱，那么查询的结果和虚拟表的列名的数据类型，就无法保持一致，这会导致查询失败。而按照列名取值，则不会发生这种情况。
SQL注入
sql注入就是利用一些sql关键词，拼接进sql字符串中，在执行过程中出现以下意外的情况，从而对数据库中的数据造成破坏。
案例
package com.lyc.lesson;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Login {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("=====欢迎登录=====");
System.out.println("用户名：");
String user = scanner.next();
System.out.println("密码：");
String pwd = scanner.next();
System.out.print("正在登录");
for (int i = 1; i < 4; i++) {
Thread.sleep(1000);
System.out.print(". ");
}
System.out.println();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "*******");
Statement sta = conn.createStatement();
String sql = "select * from user1 where user = '"+user+"' and pwd = '"+pwd+"'";
System.out.println(sql);
ResultSet rs = sta.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功！");
String user1 = rs.getString("user");
System.out.println("欢迎你！尊敬的"+user1+"用户");
} else{
System.out.println("用户名或密码错误！！！");
}
rs.close();
sta.close();
conn.close();
}
}
如上图所示，我们输入了错误的密码，但是我们使用关键字or
拼接了一个恒成立的等式，使原本错误的sql语句变成了一个正确的sql语句，从而登录成功。这就会造成重大损失。
因此我们要将之前的处理语句 Statement sta = conn.createStatement();变为预处理语句PreparedStatement ps = conn.prepareStatement(sql);。
因此，我们对登录进行增强：
package com.lyc.lesson;
import java.sql.*;
import java.util.Scanner;
public class LoginPlus {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("≌≌≌≌≌≌≌≌≌≌欢迎登录≌≌≌≌≌≌≌≌≌≌");
// for (int i = 3; i >0; i--) {
System.out.println("用户名：");
String user = scanner.nextLine();
System.out.println("密码：");
int pwd = scanner.nextInt();
login(user,pwd);
// }
}
public static void login(String user, int pwd) throws Exception {
System.out.print("正在登录");
for (int i = 1; i < 4; i++) {
Thread.sleep(800);
System.out.print(". ");
}
System.out.println();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "*******");
String sql = "select * from user where user = ? and pwd = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,user);
ps.setInt(2,pwd);
ResultSet rs = ps.executeQuery();
if(rs.next()){
String user1 = rs.getString("user");
System.out.println("欢迎你！尊敬的"+user1+"用户");
} else{
System.out.println("用户不存在！！！");
// System.out.println("你还有"+(count-1)+"次机会！");
}
rs.close();
ps.close();
conn.close();
}
}
prepareStatement();就是将原本sql语句拼接处改为
占位符?，再使用setXxx()方法对占位符进行赋值。如：ps.setString(1,user);
ps.setInt(2,pwd);其中1：表示第一个占位符，填充user
2：表示第二个占位符，填充pwd
以此类推
注意：
setXxx的数据类型要和数据库中列名的数据类型保持一致。
1126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
