数据库编程第六章课后作业

第三题，添加信息然后输出表中记录的总数

 

package homework;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Dome1 {

	public static void main(String[] args) {
		// TODO 自动生成的方法存根
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		String url = "jdbc:mysql://localhost:3306/aaa?characterEncoding=utf-8";
		String useName= "root";
		String paswoord= "1234";
		Connection con = null;
		String sql = "INSERT INTO student (studentNo,loginPwd,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)VALUES(?,?,?,?,?,?,?,?,?,?)";
		String spl1="SELECT COUNT(*)FROM student";
		PreparedStatement pst = null;
		Statement sta = null;
		ResultSet rs = null;
		try {
			con=DriverManager.getConnection(url,useName,paswoord);
			pst=con.prepareStatement(sql);
			pst.setInt(1, 10009);
			pst.setString(2,"123");
			pst.setString(3,"王宝");
			pst.setString(4,"男");
			pst.setInt(5,2);
			pst.setString(6,"13660112539");
			pst.setString(7, "湖南衡阳");
			pst.setDate(8, new Date(new java.util.Date().getTime()));
			pst.setString(9, "a183841468@qq.com");
			pst.setString(10,"430421199307199876");
			pst.executeUpdate();
			System.out.println("插入成功");
			sta=con.createStatement();
			rs= sta.executeQuery(spl1);
			rs.next();
			int row= rs.getInt(1);
			System.out.println("总共有"+row+"条记录");
		
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		try {
			rs.close();
			sta.close();
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}

}

第四题 ，查看所有的记录

package homework;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo2 {
		public static void main(String[] args) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
			String url="jdbc:mysql://localhost:3306/aaa?characterEncoding=utf-8";
			String useName= "root";
			String paswoord= "1234"; 
			Connection conn = null;
			Statement sta = null;
			String sql = "SELECT * FROM student";
			ResultSet re = null;
			try {
				conn=DriverManager.getConnection(url,useName,paswoord);
				sta=conn.createStatement();
				 re= sta.executeQuery(sql);
				 System.out.println("学号\t"+"密码\t"+"姓名\t"+"性别\t"+"年级\t"+"电话\t\t"+"地址\t"+"出生日期\t\t"+"邮箱\t"+"\t\t身份证");
				 while (re.next()) {
						System.out.println(re.getInt("studentNo")+"\t"+re.getString("loginPwd")+"\t"+re.getString("studentName")
						+"\t"+re.getString("sex")+"\t"+re.getInt("gradeId")+"\t"+re.getString("phone")+"\t"+re.getString("address")
						+"\t"+re.getDate("bornDate")+"\t"+re.getString("email")+"\t"+re.getString("identityCard"));
						System.out.println("");
				}
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
			try {
				re.close();
				sta.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
		}
}

      第五题，实现用户登入

package homework;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Dome3 {
		public static void main(String[] args) {
			Integer name= null;
			String pasword=null;
			Scanner in = new Scanner(System.in);
			System.out.print("请输入用户学号：");
			 name =in.nextInt();
			System.out.print("请输入密码：");
			 pasword = in.next();
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
			String url="jdbc:mysql://localhost:3306/aaa?characterEncoding=utf-8";
			String useName= "root";
			String paswoord= "1234";
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet re = null;
			String sql ="SELECT * FROM student WHERE studentNo=? AND loginPwd=?";
			try {
				conn=DriverManager.getConnection(url,useName,paswoord);
				pst=conn.prepareStatement(sql);
				pst.setInt(1, name);
				pst.setString(2,pasword);
				re= pst.executeQuery();
				if (re.next()) {
					System.err.println("登入成功");
				}else {
					System.err.println("登入失败");
				}
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
			try {
				re.close();
				pst.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
		}
}