JDBC
题目一:
编写一个应用程序,输入用户名和密码,访问test数据库中t_login表(字段包括id、username、password),验证登录是否成功。
代码:
Test.java
1 package cn.edu.ccut; 2 import java.sql.*; 3 import java.util.Scanner; 4 5 public class Trest { 6 7 public static void main(String[] args) { 8 Scanner r = new Scanner(System.in); 9 String username = r.next(); 10 String password = r.next(); 11 Connection con = null; 12 Statement st = null; 13 ResultSet rs =null; 14 try { 15 Class.forName("com.mysql.jdbc.Driver"); //加载JDBC驱动器; 16 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",""); //创建数据库连接; 17 st = con.createStatement(); //创建执行SQL语句的对象; 18 rs = st.executeQuery("select * from t_login where username='"+username+"' and password='"+password+"'"); //执行SQL语句(拼串); 19 if(rs.next()){ 20 System.out.println("登陆成功"); 21 } 22 else{ 23 System.out.println("登录失败"); 24 } 25 } catch (ClassNotFoundException e) { 26 e.printStackTrace(); 27 } catch (SQLException e) { 28 e.printStackTrace(); 29 }finally{ 30 try { 31 rs.close();//关闭数据库连接; 32 st.close(); 33 con.close(); 34 } catch (SQLException e) { 35 e.printStackTrace(); 36 } 37 } 38 } 39 40 }
运行结果:
题目二:
在上一题基础上,当登录成功后,将t_user表(id、name、sex、birthday)的信息进行显示(要求使用DB.java完成登录和获取t_user表中数据的操作),最后再对t_user表进行一条记录的添加操作。
代码:
1、Test.java
1 package cn.edu.ccut2; 2 3 import java.sql.SQLException; 4 import java.util.Scanner; 5 6 public class Test { 7 8 public static void main(String[] args) { 9 Scanner in = new Scanner(System.in); 10 String username = in.next(); 11 String password = in.next(); 12 Object []a = {username, password}; 13 String sql = "select * from t_login where username=? and password=?"; 14 DB db = DB.getInstance(); 15 db.excuteSelect(sql, a); 16 try { 17 if(db.rs.next()){ 18 System.out.println("登陆成功!"); 19 Object []b = new Object[0]; 20 sql = "select * from t_user "; 21 db.excuteSelect(sql, b); 22 String mname = null; 23 String msex = null; 24 String mbirthday = null; 25 while(db.rs.next()){//输出显示t_user表内容; 26 mname = db.rs.getString(2); 27 msex = db.rs.getString(3); 28 mbirthday = db.rs.getString(4); 29 System.out.println("name:"+mname+" sex:"+msex+" birthday:"+mbirthday+""); 30 } 31 System.out.println("请输入想要添加的一条记录:"); 32 String newname = in.next(); 33 String newsex = in.next(); 34 String newbirthday = in.next(); 35 Object []c = {newname, newsex , newbirthday}; 36 sql = "insert into t_user (name,sex,birthday) values(?,?,?)"; 37 int count = db.executModify(sql, c); //添加记录; 38 if(count == 0){ 39 System.out.println("插入失败!"); 40 }else{ 41 System.out.println("插入成功!"); 42 } 43 }else{ 44 System.out.println("登录失败!"); 45 } 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 } 49 } 50 51 }
2、DB.java
1 package cn.edu.ccut2; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 9 public class DB { 10 private static DB db; 11 Connection con = null; 12 PreparedStatement pre = null; 13 ResultSet rs = null; 14 public static DB getInstance(){ 15 if(db == null){ 16 db = new DB(); 17 } 18 return db; 19 } 20 private DB(){//构造方法创建数据库连接; 21 try { 22 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root",""); 23 } catch (SQLException e) { 24 e.printStackTrace(); 25 } 26 } 27 static{//静态代码块,提高程序执行效率; 28 try { 29 Class.forName("com.mysql.jdbc.Driver"); 30 } catch (ClassNotFoundException e) { 31 e.printStackTrace(); 32 } 33 } 34 public ResultSet excuteSelect(String sql , Object []args){//数据库查询操作; 35 try { 36 pre = con.prepareStatement(sql); 37 if(args.length > 0){ 38 for(int i = 0; i < args.length; i++){ 39 pre.setObject(i+1, args[i]); 40 } 41 } 42 rs = pre.executeQuery(); 43 } catch (SQLException e) { 44 e.printStackTrace(); 45 } 46 return rs; 47 } 48 public int executModify(String sql, Object []args){//数据库增/删/改操作; 49 int n = 0; 50 try { 51 pre = con.prepareStatement(sql); 52 if(args.length > 0){ 53 for(int i = 0; i < args.length; i++){ 54 pre.setObject(i+1, args[i]); 55 } 56 } 57 n = pre.executeUpdate(); 58 } catch (SQLException e) { 59 e.printStackTrace(); 60 } 61 return n; 62 } 63 public void close(){ 64 try { 65 if(rs != null){//关闭链接; 66 rs.close(); 67 } 68 con.close(); 69 pre.close(); 70 } catch (SQLException e) { 71 e.printStackTrace(); 72 } 73 } 74 }
运行结果: