第15周作业
题目1:编写一个应用程序,输入用户名和密码,访问test数据库中t_login表(字段包括id、username、password),验证登录是否成功。
一、代码
1.Test.java
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class Test { public static void main(String[] args) throws SQLException{ Connection con = null; Statement st = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } //加载驱动程序 con=DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","0000"); //创建数据库连接对象 st=con.createStatement(); Scanner reader = new Scanner(System.in); System.out.println("请输入用户名:"); String username=reader.next(); System.out.println("请输入密码:"); String password=reader.next(); rs = st.executeQuery(" select*from t_login where username= '"+username+"' and password='"+password+"'"); //创建执行Sql语句 if(rs.next()){ System.out.println("登录成功"); } else{ System.out.println("登录失败"); } if(rs!=null){ rs.close(); } st.close(); con.close(); } }
运行结果
题目2:在上一题基础上,当登录成功后,将t_user表(id、name、sex、birthday)的信息进行显示(要求使用DB.java完成登录和获取t_user表中数据的操作),最后再对t_user表进行一条记录的添加操作。
1.DB.java
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DB { private Connection con; private PreparedStatement pre; ResultSet rs; private static DB db; public static DB getInstance() { if(db ==null) { db = new DB(); } return db; } DB(){ try { con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","0000"); } catch (SQLException e) { e.printStackTrace(); } } static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public ResultSet executeSelect(String sql,Object[] args) { try { pre = con.prepareStatement(sql); if(args.length > 0) { for(int i = 0; i < args.length; i++) { pre.setObject(i+1, args[i]); } } rs = pre.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; } public int executeUpdate(String sql,Object[] args) { int n = 0; try { pre = con.prepareStatement(sql); if(args.length > 0) { for(int i = 0; i < args.length; i++) { pre.setObject(i+1, args[i]); } } n = pre.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return n; } public void close(){ try { if(rs!=null) { rs.close(); } pre.close(); con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
2.Test.java
import java.sql.SQLException; import java.util.Scanner; public class Test { public static void main(String[] args) { Scanner in = new Scanner(System.in); System.out.println("输入用户名"); String username = in.next(); System.out.println("输入用户名"); String password = in.next(); String sql = "select * from t_login where username=? and password=?"; DB db = DB.getInstance(); Object []a = {username, password}; db.executeSelect(sql, a); try { if(db.rs.next()){ System.out.println("登陆成功!"); Object []b = new Object[0]; sql = "select * from t_user "; db.executeSelect(sql, b); while(db.rs.next()){ String name = db.rs.getString(2); String sex = db.rs.getString(3); String birthday = db.rs.getString(4); System.out.println("name:"+name+" sex"+sex+" birthday :"+birthday+""); } System.out.println("请输入一条新记录:"); String newname = in.next(); String newsex = in.next(); String newbirthday = in.next(); Object []c = {newname, newsex , newbirthday}; sql = "insert into t_user (name,sex,birthday) values(?,?,?)"; int count = db.executeUpdate(sql, c); if(count == 0){ System.out.println("插入失败"); }else{ System.out.println("插入成功"); } }else{ System.out.println("插入失败"); } } catch (SQLException e) { e.printStackTrace(); } } }
运行结果