1. 输入用户名和密码判断是否登陆成功
分别使用Statement
和 prepareStatement
执行对象来执行sql查询用户和密码,进而根据执行结果判断是否登陆成功。
下面是编写的静态登录方法login 和 plogin
JDBCutils是自己抽取的工具类,用于获取数据库连接对象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 public class jdbcdemo3 { public static boolean login (String username, String userpassword) { if (username.equals("" ) || username.equals("" )) { return false ; } Connection conn = null ; Statement stmt = null ; ResultSet rs = null ; try { conn = JDBCutils.getConnection(); stmt = conn.createStatement(); String sql = "select * from user where username = '" + username +"' and userpassword = '" +userpassword+"'" ; System.out.println(sql); rs = stmt.executeQuery(sql); return rs.next(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCutils.close(stmt, conn, rs); } return true ; } public static boolean plogin (String username, String userpassword) { if (username.equals("" ) || username.equals("" )) { return false ; } Connection conn = null ; PreparedStatement stmt = null ; ResultSet rs = null ; try { conn = JDBCutils.getConnection(); String sql = "select * from user where username = ? and userpassword = ?" ; stmt = conn.prepareStatement(sql); stmt.setString(1 ,username); stmt.setString(2 ,userpassword); rs = stmt.executeQuery(); return rs.next(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCutils.close(stmt, conn, rs); } return true ; } public static void main (String[] args) { String user; String password; Scanner scanner = new Scanner(System.in); user = scanner.nextLine(); password = scanner.nextLine(); boolean plogin = plogin(user, password); if (plogin == true ) { System.out.println("登陆成功!" ); } else { System.out.println("登陆失败!" ); } } }
代码运行结果
user表中的信息有 hxx 123 和 qzy 123
使用login方法时和plogin方法的运行结果
1 2 3 4 5 6 7 8 asd a' or ' a' = ' a 登陆成功! asd a' or ' a' = ' a 登陆失败!
1 2 String sql = "select * from user where username = '"+ username +"' and userpassword = '"+userpassword+"'"; String sql = "select * from user where username = 'asd' and userpassword = 'a' or 'a' = 'a';
为避免这种情况,我们使用占位符和prepareStatement。sql语句中需要的参数位置使用 ?
号代替。然后将sql传递给prepareStatement对象,并且使用该对象中的成员方法setXxxx()对?号所代表的变量赋值。
1 2 3 4 String sql = "select * from user where username = ? and userpassword = ?" ; stmt = conn.prepareStatement(sql); stmt.setString(1 ,username); stmt.setString(2 ,userpassword);
2. sql语句中单双引号’”+ username +”‘ and userpassword = ‘“+userpassword+”‘“的问题
首先上面的sql是一个字符串,对这个字符串应该做出如下拆分来理解。
1 "select * from user where username = '"+ username +"' and userpassword = '"+userpassword+"'"
1:"select * from user where username = '"
这里的双引号相当于定义了一个字符串select * from user where username = '
2:+ username +
这里是用+号连接了username这个字符串变量
3:"' and userpassword = '"
这里的双引号相当于定义了一个字符串' and userpassword = '
4:+userpassword+
这里是用+号连接了userpassword这个字符串变量
5:'
单独的单引号
6: 整体看单引号的用处,是因为user表中username和userpassword字段是字符串类型,那么等于号后面的内容要用单引号括起来。注意:下面单引号和双引号之间不可以有空格!就相当于在本身传递的username上加了一个空格