Java数据库---DAO使用

最近学习了相关java数据库相关的知识，于是写了一个小程序来实现。

使用DAO模式，编写一个控制台程序。
输入1后，输出全部学生信息，每行显示一个学生；
输入2后，根据提示输入学号，然后输出某个学生信息或提示未找到学生；
输入3后，根据提示输入一个学生的全部信息后，提示增加成功；
输入4后，先输入要修改的学号，如果没有该学生，提示未找到，如果找到该学生，提示修改该学生的其他信息（交互界面自行设计）；
输入5后，输入要修改的学号，提示用户是否删除，如用户确定则删除学生。
以上5个操作结束后都返回主菜单。

以下便是相关代码
HelloDB.java(主程序)

package cn.edu.hit;

import java.util.List;
import java.util.Scanner;

import cn.edu.hit.dao.Studentdao;
import cn.edu.hit.entity.Student;

public class HelloDB {

	public static void main(String[] args) {
            String sid = null;
            String sname = null;
            String gender = null;
            int age = 0;
            String birthday = null;
            Scanner input = new Scanner(System.in);
            int num;
			do
            {
                System.out.println("1.查询全部学生" + "\n" + "2.按学号查询学生"+ "\n" + "3.增加学生" + "\n" + "4.修改学生信息"+ "\n" + "5.删除学生" +"\n" +"6.退出" +"\n" + "请选择");
                num = input.nextInt();
            	if(num == 1)
                {
                	Studentdao dao = new Studentdao();
                	List<Student> stuList = dao.getstudents("select * from student");
                    for(int i=0;i<stuList.size(); i++)
                    {
                    	sid =  stuList.get(i).getSid();
                        sname = stuList.get(i).getSname();
                        gender = stuList.get(i).getGender();
                        age = stuList.get(i).getAge();
                        birthday = stuList.get(i).getBirthday();
                        System.out.println(sid + "\t" + sname + "\t" + gender + "\t" + age + "\t" + birthday);
                    }
                }
                if(num == 2)
                {
                	Studentdao dao = new Studentdao();
                	System.out.println("请输入你要查询的学号：");
                	String sid_find = input.next();
                	Student ss = dao.getBySid(sid_find);
                	if(ss != null)
                	{
                		System.out.println(ss.getSid() + "\t" + ss.getSname() + "\t" + ss.getGender() + "\t" + ss.getAge() + "\t" + ss.getBirthday());
                	}
                	else
                	{
                		System.out.println("查无此人");
                	}
                }
                if(num == 3)
                {
                	System.out.println("请输入学号：");
                    sid = input.next();
                    System.out.println("请输入姓名：");
                    sname = input.next();
                    System.out.println("请输入性别：");
                    gender = input.next();
                    System.out.println("请输入年龄：");
                    age = input.nextInt();
                    System.out.println("请输入生日：");
                    birthday = input.next();
                    Student s = new Student(sid,sname,gender,age,birthday);
                    Studentdao dao = new Studentdao();
                    dao.add(s);
                    System.out.println("增加成功！");
                }
                if(num == 4)
                {
                	Studentdao dao = new Studentdao();
                	System.out.println("请输入你要修改的学号：");
                	String sid_find = input.next();
                	Student ss = dao.getBySid(sid_find);
                	if(ss != null)
                	{
                		System.out.println("请输入修改后学号：");
                		sid = input.next();
                        ss.setSid(sid);
                        System.out.println("请输入修改后姓名：");
                        sname = input.next();
                        ss.setSname(sname);
                        System.out.println("请输入修改后性别：");
                        gender = input.next();
                        ss.setGender(gender);
                        System.out.println("请输入修改后年龄：");
                        age = input.nextInt();
                        ss.setAge(age);
                        System.out.println("请输入修改后生日：");
                        birthday = input.next();
                        ss.setBirthday(birthday);
                        dao.modify(ss);
                	}
                	else
                	{
                		System.out.println("查无此人");
                	}
                }
                if(num == 5)
                {
                	Studentdao dao = new Studentdao();
                	System.out.println("请输入你要修改的学号：");
                	String sid_find = input.next();
                	Student ss = dao.getBySid(sid_find);
                	if(ss != null)
                	{
                		System.out.println("你确定要删除？(Y/N)");
                		String requests = input.next();
                		if(requests.equals("Y"))
                		{
                			dao.remove(sid_find);
                			System.out.println("已删除");
                		}
                		else
                		{
                			System.out.println("请选择其他操作");
                		}
                	}
                	else
                	{
                		System.out.println("查无此人");
                	}
                }
            }while(num != 6);
			System.exit(0);
	}
}

student.java（记录学生信息）

package cn.edu.hit.entity;

public class Student {
	private String sid;
	private String sname;
	private String gender;
	private int age;
	private String birthday;
	public Student() {
		super();
	}
	public Student(String sid, String sname, String gender, int age, String birthday) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.gender = gender;
		this.age = age;
		this.birthday = birthday;
	}
	public String getSid() {
		return sid;
	}
	public void setSid(String sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getBirthday() {
		return birthday;
	}
	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}
}

Studnetdao.java（实现增删改查）

package cn.edu.hit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.edu.hit.entity.Student;
import cn.edu.hit.utils.DbUtils;

public class Studentdao {
	public void add(Student s)
	{
		String sid = s.getSid();
        String sname = s.getSname();
        String gender = s.getGender();
        int age = s.getAge();
        String birthday = s.getBirthday();
        String sql = "insert into student values('" + sid + "','" + sname + "','" + gender + "','" +  age + "','" + birthday + "')";
        DbUtils du = new DbUtils();
        du.executeUpdate(sql);
        du.close();
	}
	
	public void remove(String sid)
	{
		String sql = "delete from student where sid = '" + sid + "'";
        DbUtils du = new DbUtils();
        du.executeUpdate(sql);
        du.close();
	}
	
	public void modify(Student s)
	{
		String sid = s.getSid();
        String sname = s.getSname();
        String gender = s.getGender();
        int age = s.getAge();
        String birthday = s.getBirthday();
        String sql = "update student set sname = '"+ sname + 
        		"',gender = '"+ gender + "',age = "+ age + 
        		",birthday = '"+ birthday + "'where sid ="
        		+ "'"+ sid +"'";
        DbUtils du = new DbUtils();
        du.executeUpdate(sql);
        du.close();
	}
	
	public List<Student> getstudents(String sql)
	{
		DbUtils du = new DbUtils();
        ResultSet rs = du.excuteQuery(sql);
        List<Student> stuList = new ArrayList<>();
        String sid = null;
        String sname = null;
        String gender = null;
        int age =  0;
        String birthday = null;
        try {
			while(rs.next())
			{
				sid = rs.getString(1);
				sname = rs.getString(2);
				gender = rs.getString(3);
				age = rs.getInt(4);
				birthday = rs.getString(5);
				stuList.add(new Student(sid, sname, gender, age, birthday));
			}
			du.close();
			return stuList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public Student getBySid(String sid)
	{
		DbUtils du = new DbUtils();
		String sql = "select * from student where sid = '" + sid + "'";
        ResultSet rs = du.excuteQuery(sql);
        String sname = null;
        String gender = null;
        int age =  0;
        String birthday = null;
        try {
			while(rs.next())
			{
				sname = rs.getString(2);
				gender = rs.getString(3);
				age = rs.getInt(4);
				birthday = rs.getString(5);
			}
			du.close();
			return  new Student(sid , sname ,gender ,age ,birthday);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
}

DBUtils.java(实现对数据库的操作)

package cn.edu.hit.utils;

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


public class DbUtils {
	private Connection con;
	public DbUtils() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/hit?useSSL=false&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull","root","//输自己的密码");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void close() {
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ResultSet excuteQuery(String sql) {
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
	}
	
	public void executeUpdate(String sql) {
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

于是我们就大功告成啦！