第十五周作业
题目1:编写一个应用程序,输入用户名和密码,访问test数据库中t_login表(字段包括id、username、password),验证登录是否成功。
代码
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 sql { public static void main(String[] args) { // TODO Auto-generated method stub Scanner reader=new Scanner(System.in); System.out.println("输入username"); String username=reader.next(); System.out.println("输入password"); String password=reader.next(); try { Class.forName("com.mysql.jdbc.Driver");//加载驱动器 Connection con=DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","1");//链接数据库 Statement st=con.createStatement(); ResultSet rs=st.executeQuery( "select * from t_login where username='"+username+"' and password='"+password+"'");//sql语句 if(rs.next()){ System.out.println("Yes"); }else{ System.out.println("NO"); } if(rs != null){ rs.close(); } st.close(); con.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
运行结果:
题目2:在上一题基础上,当登录成功后,将t_user表(id、name、sex、birthday)的信息进行显示(要求使用DB.java完成登录和获取t_user表中数据的操作),最后再对t_user表进行一条记录的添加操作。
代码:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DB { private static DB db; Connection con = null; PreparedStatement pre = null; ResultSet rs = null; public static DB getInstance(){ if(db == null){ db = new DB(); } return db; } private DB(){//构造方法创建数据库连接; try { con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","1"); } catch (SQLException e) { e.printStackTrace(); } } static{//静态代码块,提高程序执行效率; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public ResultSet excuteSelect(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 executModify(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(); } con.close(); pre.close(); } catch (SQLException e) { e.printStackTrace(); } } }
import java.sql.SQLException; import java.util.Scanner; public class sql1 { public static void main(String[] args) { Scanner in = new Scanner(System.in); String username = in.next(); String password = in.next(); Object []a = {username, password}; String sql = "select * from t_login where username=? and password=?"; DB db = DB.getInstance(); db.excuteSelect(sql, a); try { if(db.rs.next()){ System.out.println("登陆成功!"); Object []b = new Object[0]; sql = "select * from t_user "; db.excuteSelect(sql, b); String mname = null; String msex = null; String mbirthday = null; while(db.rs.next()){//输出显示t_user表内容; mname = db.rs.getString(2); msex = db.rs.getString(3); mbirthday = db.rs.getString(4); System.out.println("name:"+mname+" sex:"+msex+" birthday:"+mbirthday+""); } 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.executModify(sql, c); //添加记录; if(count == 0){ System.out.println("插入失败!"); }else{ System.out.println("插入成功!"); } }else{ System.out.println("登录失败!"); } } catch (SQLException e) { e.printStackTrace(); } } }