数据库层
package com.imooc.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL="jdbc:mysql://127.0.0.1:3306/mytest";
private static final String USER="root";
private static final String PASSWORD="ztt201314";
private static Connection conn = null;
static{
try {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接
conn=DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
return conn;
}
}
模型层
package com.imooc.model;
public class Student {
private String name;
private Integer mathGrade;
private Integer chineseGrade;
private Integer englishGrade;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getMathGrade() {
return mathGrade;
}
public void setMathGrade(Integer mathGrade) {
this.mathGrade = mathGrade;
}
public Integer getChineseGrade() {
return chineseGrade;
}
public void setChineseGrade(Integer chineseGrade) {
this.chineseGrade = chineseGrade;
}
public Integer getEnglishGrade() {
return englishGrade;
}
public void setEnglishGrade(Integer englishGrade) {
this.englishGrade = englishGrade;
}
//重写toString()方法
@Override
public String toString() {
return "Student [name=" + name + ", mathGrade=" + mathGrade
+ ", chineseGrade=" + chineseGrade + ", englishGrade="
+ englishGrade + "]";
}
}
功能层
package com.imooc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.imooc.db.DBUtil;
import com.imooc.model.Student;
public class StudentDao {
public void addStudent(Student s) throws Exception{
Connection conn = DBUtil.getConnection();
String sql = "" +
" INSERT INTO mytable1 " +
" (name,mathGrade,chineseGrade,englishGrade) " +
" VALUES(?,?,?,?) ";
PreparedStatement ptmt = conn.prepareStatement(sql); //预编译执行SQL语句
ptmt.setString(1,s.getName());
ptmt.setInt(2, s.getMathGrade());
ptmt.setInt(3, s.getChineseGrade());
ptmt.setInt(4, s.getEnglishGrade());
ptmt.execute(); //真正执行SQL语句
}
public void updateStudent(Student s) throws SQLException{
Connection conn = DBUtil.getConnection();
String sql = "" +
" UPDATE mytable1 " +
" SET mathGrade=?,chineseGrade=?,englishGrade=? " +
" WHERE name=? ";
PreparedStatement ptmt = conn.prepareStatement(sql); //预编译执行SQL语句
ptmt.setInt(1, s.getMathGrade());
ptmt.setInt(2, s.getChineseGrade());
ptmt.setInt(3, s.getEnglishGrade());
ptmt.setString(4,s.getName());
ptmt.execute(); //真正执行SQL语句
}
public void delStudent(String name) throws SQLException{
Connection conn = DBUtil.getConnection();
String sql = "" +
" DELETE FROM mytable1 " +
" WHERE name=? ";
PreparedStatement ptmt = conn.prepareStatement(sql); //预编译执行SQL语句
ptmt.setString(1,name);
ptmt.execute(); //真正执行SQL语句
}
///对查询方法进行重载,可以实现按照不同字段名进行查询/
public List<Student> query() throws Exception{
List<Student> stu = new ArrayList<Student>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM mytable1");
PreparedStatement ptmt
=conn.prepareStatement(sb.toString()); //预编译执行SQL语句
ResultSet rs=ptmt.executeQuery(); //真正执行SQL语句
Student s = null;
while(rs.next()){
s = new Student();
s.setName(rs.getString("name"));
s.setMathGrade(rs.getInt("mathGrade"));
s.setChineseGrade(rs.getInt("chineseGrade"));
s.setEnglishGrade(rs.getInt("englishGrade"));
stu.add(s);
}
return stu;
}
//优化查询方法1,按照指定条件(特定的字段名)查询数据
/* public List<Student> query(String name) throws Exception{
List<Student> stu = new ArrayList<Student>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM mytable1 ");
sb.append("WHERE name=? ");
PreparedStatement ptmt
=conn.prepareStatement(sb.toString());
ptmt.setString(1, name);
System.out.println(sb.toString());
ResultSet rs=ptmt.executeQuery();
Student s = null;
while(rs.next()){
s = new Student();
s.setName(rs.getString("name"));
s.setMathGrade(rs.getInt("mathGrade"));
s.setChineseGrade(rs.getInt("chineseGrade"));
s.setEnglishGrade(rs.getInt("englishGrade"));
stu.add(s);
}
return stu;
}*/
//优化查询方法2,按照指定条件(特定的字段名)查询数据
public List<Student> query(String name,int mathGrade) throws Exception{
List<Student> stu = new ArrayList<Student>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM mytable1 ");
sb.append("WHERE name=? AND mathGrade=? ");
PreparedStatement ptmt= conn.prepareStatement(sb.toString());
ptmt.setString(1, name);
ptmt.setInt(2, mathGrade);
System.out.println(sb.toString());
ResultSet rs=ptmt.executeQuery();
Student s = null;
while(rs.next()){
s = new Student();
s.setName(rs.getString("name"));
s.setMathGrade(rs.getInt("mathGrade"));
s.setChineseGrade(rs.getInt("chineseGrade"));
s.setEnglishGrade(rs.getInt("englishGrade"));
stu.add(s);
}
return stu;
}
//优化查询方法3,按照指定条件(LIKE,%)查询数据
public List<Student> query(String name) throws Exception{
List<Student> stu = new ArrayList<Student>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM mytable1 ");
sb.append("WHERE name LIKE ? ");
PreparedStatement ptmt =conn.prepareStatement(sb.toString());
ptmt.setString(1, "%"+name+"%");
ResultSet rs=ptmt.executeQuery();
Student s = null;
while(rs.next()){
s = new Student();
s.setName(rs.getString("name"));
s.setMathGrade(rs.getInt("mathGrade"));
s.setChineseGrade(rs.getInt("chineseGrade"));
s.setEnglishGrade(rs.getInt("englishGrade"));
stu.add(s);
}
return stu;
}
public Student get (String name) throws SQLException{
Student stu = null;
Connection conn = DBUtil.getConnection();
String sql = "" +
" SELECT * FROM mytable1 " +
" WHERE name=? ";
PreparedStatement ptmt = conn.prepareStatement(sql); //预编译执行SQL语句
ptmt.setString(1,name);
ResultSet rs = ptmt.executeQuery();
while(rs.next()){
stu = new Student();
stu.setName(rs.getString("name"));
stu.setMathGrade(rs.getInt("mathGrade"));
stu.setChineseGrade(rs.getInt("chineseGrade"));
stu.setEnglishGrade(rs.getInt("englishGrade"));
}
return stu;
}
}
控制层
package com.imooc.action;
import java.util.List;
import com.imooc.dao.StudentDao;
import com.imooc.model.Student;
public class StudentAction {
public void add(Student s) throws Exception{
StudentDao SD = new StudentDao();
SD.addStudent(s);
}
public void edit(Student s) throws Exception{
StudentDao SD = new StudentDao();
SD.updateStudent(s);
}
public void del(String name) throws Exception{
StudentDao SD = new StudentDao();
SD.delStudent(name);
}
public List<Student> query() throws Exception{
StudentDao SD = new StudentDao();
return SD.query();
}
//按照字段名(姓名)查询
public List<Student> query(String name) throws Exception{
StudentDao SD = new StudentDao();
return SD.query(name);
}
//查询个人数据
public Student get(String name) throws Exception{
StudentDao SD = new StudentDao();
return SD.get(name);
}
}
测试层
package com.imooc.test;
import java.util.List;
import com.imooc.dao.StudentDao;
import com.imooc.model.Student;
public class Test1 {
//main方法测试数据库的连接情况,与增删改查的实现情况
//即测试StudentDao.java
public static void main(String[] args) throws Exception {
//Test1:数据库连接 与数据库的全部查询测试
/*StudentDao stu = new StudentDao();
List <Student> s = stu.query();
for(Student student : s){
System.out.print(student.getName() + " ");
System.out.print(student.getMathGrade() + " ");
System.out.print(student.getChineseGrade() + " ");
System.out.print(student.getEnglishGrade() + " ");
System.out.println();
}
System.out.println(s.size()); //返回列表中的元素数。
*/
//Test2:添加数据测试
/* StudentDao stu = new StudentDao();
Student s = new Student();
s.setName("小I");
s.setMathGrade(87);
s.setChineseGrade(89);
s.setEnglishGrade(55);
stu.addStudent(s);*/
//Test3:更新数据测试
/*StudentDao stu = new StudentDao();
Student s = new Student();
s.setMathGrade(20);
s.setChineseGrade(89);
s.setEnglishGrade(55);
s.setName("小I");
stu.updateStudent(s);*/
//Test4:删除数据测试
/*StudentDao stu = new StudentDao();
stu.delStudent("小I");*/
//Test5:单个数据查询测试
/*StudentDao stu = new StudentDao();
Student s = new Student();
s = stu.get("小Q");
System.out.println(s.toString());*/
//按照特定的字段名查询数据1
/*StudentDao stu = new StudentDao();
List <Student> s = stu.query("小Q");
for(int i = 0; i < s.size(); i++){
System.out.println(s.get(i).toString());
}
System.out.println(s.size());*/
//按照特定的字段名查询数据2
/*StudentDao stu = new StudentDao();
List <Student> s = stu.query("小Q",76);
for(int i = 0; i < s.size(); i++){
System.out.println(s.get(i).toString());
}
System.out.println(s.size());*/
//按照特定的字段名查询数据3
/*StudentDao stu = new StudentDao();
//查询姓小同学的数据
List <Student> s = stu.query("小%");
for(int i = 0; i < s.size(); i++){
System.out.println(s.get(i).toString());
}
System.out.println(s.size());*/
}
}
package com.imooc.test;
import java.util.List;
import com.imooc.action.StudentAction;
import com.imooc.model.Student;
public class Test2 {
//main方法测试控制层的实现情况
//即测试StudentAction.java
public static void main(String[] args) throws Exception {
//查询数据测试
/*StudentAction action = new StudentAction();
List<Student> stu = action.query();
//遍历方式1,打印结果形如(Student [name=小Q, mathGrade=76, chineseGrade=88, englishGrade=90])
for(int i = 0; i < stu.size(); i++){
System.out.println(stu.get(i).toString());
}
//遍历方式2,打印结果形如(小Q 76 88 90 )
for(Student student : stu){
System.out.print(student.getName() + " ");
System.out.print(student.getMathGrade() + " ");
System.out.print(student.getChineseGrade() + " ");
System.out.print(student.getEnglishGrade() + " ");
System.out.println();
}*/
//添加数据测试
/*StudentAction action = new StudentAction();
Student s = new Student();
s.setName("小O");
s.setMathGrade(90);
s.setChineseGrade(89);
s.setEnglishGrade(80);
action.add(s);*/
//更新数据测试
/*StudentAction action = new StudentAction();
Student s = new Student();
s.setName("小O");
s.setMathGrade(97);
s.setChineseGrade(86);
s.setEnglishGrade(88);
action.edit(s);*/
//删除数据测试
/*StudentAction action = new StudentAction();
action.del("小T");*/
StudentAction action = new StudentAction();
List<Student> stu = action.query("小Q");
//打印结果形如(Student [name=小Q, mathGrade=76, chineseGrade=88, englishGrade=90])
for(int i = 0; i < stu.size(); i++){
System.out.println(stu.get(i).toString());
}
//打印结果形如(小Q 76 88 90 )
for(Student student : stu){
System.out.print(student.getName() + " ");
System.out.print(student.getMathGrade() + " ");
System.out.print(student.getChineseGrade() + " ");
System.out.print(student.getEnglishGrade() + " ");
System.out.println();
}
}
}
视图层
package com.imooc.view;
import java.util.List;
import java.util.Scanner;
import com.imooc.action.StudentAction;
import com.imooc.model.Student;
public class View {
private static final String CONTEXT =
"欢迎来到学生管理系统:\n" +
"下面是学生管理系统的功能列表:\n" +
"[QUERY/Q]查看全部学生信息\n" +
"[GET/G]查看某位学生信息\n" +
"[ADD/A]添加学生信息\n" +
"[UPDATE/U]更新学生信息\n" +
"[DELETE/D]删除学生信息\n" +
"[SELECT/S]查询学生信息(根据姓名查询)\n" +
"[EXIT/E]退出学生管理系统\n";
private static final String OPERATION_QUERY="QUERY";
private static final String OPERATION_GET="GET";
private static final String OPERATION_ADD="ADD";
private static final String OPERATION_UPDATE="UPDATE";
private static final String OPERATION_DELETE="DELETE";
private static final String OPERATION_SELECT="SELECT";
private static final String OPERATION_EXIT="EXIT";
public static void main(String[] args) {
Student stu = new Student();StudentAction action = new StudentAction();String prenious = null; //记忆变量
System. out.println( CONTEXT);Scanner input = new Scanner(System.in);
//如果有输入,则返回 true
while(input.hasNext()){
String in = input.next().toString();
//通过字符串的截取实现输出"EXIT"与'E',退出循环if( OPERATION_EXIT.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())){System. out.println( "您已成功退出学生管理系统!!");break;}
//通过字符串的截取实现输出"SELECT"与'S',查询数据else if( OPERATION_SELECT.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_SELECT.substring(0, 1).equals(in.toUpperCase())){
try {
System. out.println( "请输入查询学生的姓名:");String stuName = input.next();List<Student> list = action.query(stuName);for(Student student : list){
System. out.print(student.getName() + " ");System. out.print(student.getMathGrade() + " ");System. out.print(student.getChineseGrade() + " ");System. out.print(student.getEnglishGrade() + " ");System. out.println();
}} catch (Exception e) {System. out.println( "查询数据失败");
e.printStackTrace();
}
}
//通过字符串的截取实现输出"DELETE"与'D',删除数据else if( OPERATION_DELETE.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())){
try {
System. out.println( "请输入删除学生的姓名:");String stuName = input.next();action.del(stuName);System. out.println( "删除成功");
} catch (Exception e) {
System. out.println( "删除失败");e.printStackTrace();
}
}
//通过字符串的截取实现输出"UPDATE"与'U',更新数据else if( OPERATION_UPDATE.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())){
try {
System. out.println( "请输入修改学生的姓名:");String stuName = input.next();stu.setName(stuName);System. out.println( "请输入修改后学生数学成绩:");int grade1 = input.nextInt();stu.setMathGrade(Integer. valueOf(grade1));System. out.println( "请输入修改后学生语文成绩:");int grade2 = input.nextInt();stu.setChineseGrade(Integer. valueOf(grade2));System. out.println( "请输入修改后学生英语成绩:");int grade3 = input.nextInt();stu.setEnglishGrade(Integer. valueOf(grade3));action.edit(stu);System. out.println( "更新数据成功");
} catch (Exception e) {
System. out.println( "更新数据失败");e.printStackTrace();
}
}
//通过字符串的截取实现输出"GET"与'G',查找单个学生信息else if( OPERATION_GET.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_GET.substring(0, 1).equals(in.toUpperCase())){
try {
System. out.println( "请输入要查询学生的姓名:");String stuName = input.next();//action.get(stuName)是一个Student对象
System. out.print(action.get(stuName).getName() + " ");System. out.print(action.get(stuName).getMathGrade() + " ");System. out.print(action.get(stuName).getChineseGrade() + " ");System. out.print(action.get(stuName).getEnglishGrade() + " ");System. out.println();System. out.println( "查询数据成功");
} catch (Exception e) {
System. out.println( "查询数据失败");e.printStackTrace();
}
}
//通过字符串的截取实现输出"QUERY"与'Q ',查询数据else if( OPERATION_QUERY.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){
try {List<Student> list = action.query();for(Student student : list){
System. out.print(student.getName() + " ");System. out.print(student.getMathGrade() + " ");System. out.print(student.getChineseGrade() + " ");System. out.print(student.getEnglishGrade() + " ");System. out.println();
}
System.out.println("查询数据成功");
} catch (Exception e) {
System. out.println( "查询数据失败");e.printStackTrace();
}
}
//通过字符串的截取实现输出"ADD"与'A',添加数据else if( OPERATION_ADD.equals(in.toUpperCase()) //大小写的转换,不易出错
|| OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())|| OPERATION_ADD.equals(prenious)){
prenious = OPERATION_ADD;System. out.println( "请输入要加入学生信息的个数:");int number = input.nextInt();for( int i = 0; i < number; i++){
System. out.println( "请输入学生姓名:");String stuName = input.next();stu.setName(stuName);System. out.println( "请输入学生数学成绩:");int grade1 = input.nextInt();stu.setMathGrade(Integer. valueOf(grade1));System. out.println( "请输入学生语文成绩:");int grade2 = input.nextInt();stu.setChineseGrade(Integer. valueOf(grade2));System. out.println( "请输入学生英语成绩:");int grade3 = input.nextInt();stu.setEnglishGrade(Integer. valueOf(grade3));
try {action.add(stu);System. out.println( "添加学生信息成功!");} catch (Exception e) {e.printStackTrace();}System.out.println("添加学生信息失败!");
}
}
}
}
}