客户端操作(主函数)Tset.java
package com.edu.test;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;
import com.edu.dao.StuDao;
import com.edu.javabean.Student;
public class Test {
public static void showMenu(){
System.out.println("请选择序号:");
System.out.println("1.增加,2.更新,3.删除,4.查询,5.退出");
}
public static void main(String[] args) throws SQLException {
Scanner scan=new Scanner(System.in);
showMenu();
int i=0;
StuDao dao=new StuDao();
while((i=scan.nextInt())!=5){
switch(i)
{
case 1:
//增加
System.out.println("请输入学生姓名:");
String name=scan.next();
System.out.println("请输入学生性别:");
String sex=scan.next();
System.out.println("请输入学生的年龄:");
int age=scan.nextInt();
System.out.println("请输入学生的成绩:");
int score=scan.nextInt();
Student student =new Student();
student.setName(name);
student.setSex(sex);
student.setAge(age);
student.setScore(score);
boolean res = dao.insertStu(student);
if(res)
System.out.println("录入学生信息成功");
showMenu();
break;
case 2:
//更新
System.out.println("请输入要更改的学生编号:");
int id=scan.nextInt();
System.out.println("请输入学生新的姓名:");
name=scan.next();
System.out.println("请输入学生新的性别:");
sex=scan.next();
System.out.println("请输入学生的新的年龄:");
age=scan.nextInt();
System.out.println("请输入学生的新的成绩:");
score=scan.nextInt();
Student student2 =new Student();
student2.setName(name);
student2.setSex(sex);
student2.setAge(age);
student2.setScore(score);
student2.setId(id);
boolean res2 = dao.updateStu(student2);
if(res2)
System.out.println("更新学生信息成功");
showMenu();
break;
case 3:
//删除
System.out.println("请输入要删除的学生编号:");
int id2=scan.nextInt();
boolean res3 = dao.deleteStu(id2);
if(res3)
System.out.println("删除学生信息成功");
showMenu();
break;
case 4:
//查询
ArrayList list=dao.getAllStus();
Iterator it= list.iterator();
System.out.println("id\tname\tsex\tage\tscore\t");
while(it.hasNext()){
Student student3=(Student) it.next();
System.out.println(student3.getId()+"\t"+student3.getName()+
"\t"+student3.getSex()+"\t"+student3.getAge()+
"\t"+student3.getScore()+"\t");
}
showMenu();
break;
default:
showMenu();
break;
}
}
System.out.println("谢谢使用,欢迎下次使用");
}
}
学生类Student.java
package com.edu.javabean;
//学生类
public class Student {
private int id;
private String name;
private String sex;
private int age;
private int score;
public Student() {
}
public Student(int id, String name, String sex, int age, int score) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.score = score;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
}
中间连接类StuDao.java
package com.edu.dao;
import java.sql.*;
import java.util.ArrayList;
import com.edu.javabean.Student;
import com.edu.util.ConnectionFactory;
//中间过渡层,连接主函数操作与数据库操作
//查询表
public class StuDao {
public ArrayList getAllStus() throws SQLException{
ArrayList list=new ArrayList();
Connection conn=ConnectionFactory.getConnection();
String sql="select * from student";
PreparedStatement pstmt=conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
Student stu=null;
while(rs.next()){
stu = new Student(rs.getInt(1), rs.getString(2), rs.getString(3),
rs.getInt(4), rs.getInt(5));
list.add(stu);
}
rs.close();
pstmt.close();
conn.close();
return list;
}
//插入表
public boolean insertStu(Student student) throws SQLException{
Connection conn=ConnectionFactory.getConnection();
String sql="insert into student(name,sex,age,score) values(?,?,?,?)";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setInt(3, student.getAge());
pstmt.setInt(4, student.getScore());
int res = pstmt.executeUpdate();
pstmt.close();
conn.close();
if(res>0)
return true;
else
return false;
}
//更新表
public boolean updateStu(Student student) throws SQLException{
Connection conn=ConnectionFactory.getConnection();
String sql="update student set name=?,sex=?,age=?,score=? where id=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setInt(3, student.getAge());
pstmt.setInt(4, student.getScore());
pstmt.setInt(5, student.getId());
int res = pstmt.executeUpdate();
pstmt.close();
conn.close();
if(res>0)
return true;
else
return false;
}
//删除行
public boolean deleteStu(int id) throws SQLException{
Connection conn=ConnectionFactory.getConnection();
String sql="delete from student where id=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
int res = pstmt.executeUpdate();
pstmt.close();
conn.close();
if(res>0)
return true;
else
return false;
}
}
底层数据库操作类ConnectionFactory.java
package com.edu.util;
import java.sql.*;
//直接操作数据库
public class ConnectionFactory {
private static ConnectionFactory ref = new ConnectionFactory();
private ConnectionFactory() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
String url="jdbc:mysql:///test?useSSL=true";
return DriverManager.getConnection(url, "root", "root");
}
public static void close(ResultSet rs){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(PreparedStatement pstmt){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(Connection conn){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
将数据库操作ConnectionFactory与前端操作mian函数分隔开,使其分层,使用中间层StuDao访问,降低数据库访问压力,提高数据库安全性