publicclassDemo02{publicstaticvoidmain(String[] args)throwsSQLException{//1.获取数据库连接;Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","123456");System.out.println("连接对象:"+conn);//2.创建执行SQL语句的对象Statement s = conn.createStatement();//3.执行SQL语句
s.execute("drop table jdbct1");//4.关闭资源
conn.close();System.out.println("执行完成!");}}
2.插入 修改 和查询表
public class Demo03 {
public static void main(String[] args) throws SQLException {
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","123456");
Statement s = conn.createStatement();// s.executeUpdate("insert into emp(name) values('Tom')");//执行修改// s.executeUpdate("update emp set name='jerry' where name='Tom' ");// s.executeUpdate("delete from emp where name ='jerry'");//执行查询数据的SQL
ResultSet rs = s.executeQuery("select * from emp");//遍历结果集对象 rs.next() 询问是否有下一条数据,有返回true否则返回falsewhile(rs.next()){
String name = rs.getString("name");double sal = rs.getDouble("sal");
System.out.println(name+":"+sal);
}
conn.close();
System.out.println("执行完成");
}
}
publicclassDemo04{publicstaticvoidmain(String[] args){//获取连接对象try(Connection conn =DBUtils.getConn()){//创建执行SQl语句对象Statement s = conn.createStatement();ResultSet rs = s.executeQuery("select name from emp");while(rs.next()){String name = rs.getString("name");System.out.println(name);}}catch(SQLException throwables){
throwables.printStackTrace();}}}
publicclassDemo06{publicstaticvoidmain(String[] args){Scanner sc =newScanner(System.in);System.out.println("请输入用户名");String username=sc.nextLine();System.out.println("请输入密码");String password=sc.nextLine();System.out.println("请输入昵称");String nick=sc.nextLine();try(Connection conn =DBUtils.getConn()){Statement s = conn.createStatement();//查询user表中是否存在usernameResultSet rs = s.executeQuery("select id from user where username='"+ username +"'");if(rs.next()){System.out.println("用户名已存在");return;}String sql="insert into user values(null,'"+username+"','"+password+"','"+nick+"')";
s.executeUpdate(sql);System.out.println("注册成功!");}catch(SQLException throwables){
throwables.printStackTrace();}}}
6.寻找SQL注入漏洞
publicclassDemo07{publicstaticvoidmain(String[] args){Scanner sc =newScanner(System.in);System.out.println("请输入用户名");String username = sc.nextLine();System.out.println("请输入密码");String password = sc.nextLine();try(Connection conn =DBUtils.getConn()){Statement s = conn.createStatement();String sql="select count(*) from user where username='"+username+"' and password='"+password+"'";System.out.println(sql);ResultSet rs = s.executeQuery(sql);//让游标往下移动 指向返回的数据
rs.next();//取出查询到的数量 1代表查询到的数据位置int count = rs.getInt(1);if(count>0){System.out.println("登录成功!");}else{System.out.println("用户名或密码错误!");}}catch(SQLException throwables){
throwables.printStackTrace();}}}
7.防止SQL注入,采用预编译形式
publicclassDemo07{publicstaticvoidmain(String[] args){Scanner sc =newScanner(System.in);System.out.println("请输入用户名");String username = sc.nextLine();System.out.println("请输入密码");String password = sc.nextLine();try(Connection conn =DBUtils.getConn()){// Statement s = conn.createStatement();// String sql="select count(*) from user where username='"+username+"' and password='"+password+"'";// System.out.println(sql);// ResultSet rs = s.executeQuery(sql);通过PreparedStatement对象解决SQL注入的问题 Admin adminString sql="select count(*) from user where username=? and password=?";PreparedStatement ps = conn.prepareStatement(sql);//把?替换成用户名和密码 1和2 代表?的位置
ps.setString(1,username);
ps.setString(2,password);//执行SQL语句ResultSet rs = ps.executeQuery();//让游标向下移动 指向返回的数据
rs.next();//取出查询到的数量 1代表查询到的数据位置int count = rs.getInt(1);if(count>0){System.out.println("登录成功");}else{System.out.println("用户名或密码错误!");}}catch(SQLException throwables){
throwables.printStackTrace();}}}
8.登录功能复写(防注入)
publicclassDemo08{publicstaticvoidmain(String[] args){Scanner sc =newScanner(System.in);System.out.println("请输入用户名");String username = sc.nextLine();System.out.println("请输入密码");String password = sc.nextLine();try(Connection conn=DBUtils.getConn()){String sql="select password from user where username=?";PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);ResultSet rs = ps.executeQuery();if(rs.next()){if(rs.getString("password").equals(password)){System.out.println("登录成功!");}else{System.out.println("密码错误");}}else{System.out.println("用户名不存在!");}}catch(SQLException throwables){
throwables.printStackTrace();}}}
9.注册功能复写(防注入)
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;public class Demo09 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
System.out.println("请输入昵称");
String nick = sc.nextLine();//得到数据库连接
try (Connection conn = DBUtils.getConn()){
String sql="select id from user where username=?";//创建执行SQL语句的对象
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);//查询user表中是否已经存在了username
ResultSet rs = ps.executeQuery();//判断是否查询到了数据if(rs.next()){
System.out.println("用户名已存在!");return;
}
//往数据库中添加数据
String insert_sql="insert into user values (null,?,?,?)";
ps=conn.prepareStatement(insert_sql);
ps.setString(1,username);
ps.setString(2,password);
ps.setString(3,nick);
ps.executeUpdate();
System.out.println("注册成功!");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}