Java 数据库

package com.feicui.www_util;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtil {
	//定义URL变量
	private static String url="jdbc:mysql://localhost:3306/students";
	//定义用户名变量
	private static String user="root";
	//定义密码变量
	private static String password="root";
	//创建链接对象
	private static Connection conn=null;
	//通过静态代码块的形式来实现
	static {
		//注册驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url,user,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	//创建方法，把连接对象返回，这样就有了值
	public static Connection getConnection(){
		return conn;
	}
}

package com.feicui.www_entity;

public class User {
		private Integer id;
		private String username;
		private Integer age;
		private String sex;
		private Integer score;
		private String classes;
		public Integer getId() {
			return id;
		}
		public void setId(Integer id) {
			this.id = id;
		}
		public String getUsername() {
			return username;
		}
		public void setUsername(String username) {
			this.username = username;
		}
		public Integer getAge() {
			return age;
		}
		public void setAge(Integer age) {
			this.age = age;
		}
		public String getSex() {
			return sex;
		}
		public void setSex(String sex) {
			this.sex = sex;
		}
		public Integer getScore() {
			return score;
		}
		public void setScore(Integer score) {
			this.score = score;
		}
		public String getClasses() {
			return classes;
		}
		public void setClasses(String classes) {
			this.classes = classes;
		}
		public User(String username, Integer age, String sex, Integer score, String classes) {
			super();
			this.username = username;
			this.age = age;
			this.sex = sex;
			this.score = score;
			this.classes = classes;
		}
		public User(Integer id, String username, Integer age, String sex, Integer score, String classes) {
			super();
			this.id = id;
			this.username = username;
			this.age = age;
			this.sex = sex;
			this.score = score;
			this.classes = classes;
		}
		@Override
		public String toString() {
			return "User [id=" + id + ", username=" + username + ", age=" + age + ", sex=" + sex + ", score=" + score
					+ ", classes=" + classes + "]";
		}
}

package com.feicui.www_dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.feicui.www_entity.User;
import com.feicui.www_util.DBUtil;

public class UserDao {
	private  boolean flager;//存在
	public boolean isFlager() {
		return flager;
	}
	public void setFlager(boolean flager) {
		this.flager = flager;
	}
	/**
	 *                       添加功能
	 * @param user
	 * @throws Exception
	 */
	public void add(User user) throws Exception{
		//获得连接对象
		Connection conn=DBUtil.getConnection();
		//编写Sql语句
		String sql="insert into user(username,age,sex,score,classes)"+" values(?,?,?,?,?)";
		//预编译
		PreparedStatement ptmp = conn.prepareStatement(sql);
		//两个参数，第一个：值字段位置；第二个：具体数据
		ptmp.setString(1, user.getUsername());
		ptmp.setInt(2, user.getAge());
		ptmp.setString(3, user.getSex());
		ptmp.setInt(4, user.getScore());
		ptmp.setString(5, user.getClasses());
		//需要执行，调用执行方法
		ptmp.execute();
	}
	/**
	 *                        删除功能
	 * @param id
	 * @throws Exception
	 */
	public void delete(int id) throws Exception{
		//获得连接对象
		Connection conn=DBUtil.getConnection();
		//拼写sql语句
		String sql="delete from user where id=?";
		//预编译
		CallableStatement ptmp = conn.prepareCall(sql);
		//设置参数
		ptmp.setInt(1, id);
		//执行
		ptmp.execute();
	}
	/**
	 *                 更新（修改）功能
	 * @param user
	 * @throws Exception
	 */
	public void update(User user) throws Exception{
		//获得连接对象
		Connection conn=DBUtil.getConnection();
		//拼写sql语句
		String sql="update user set username=?,age=?,sex=?,score=?,classes=? where id=?";
		//预编译
		CallableStatement ptmp = conn.prepareCall(sql);
		//设置参数
		ptmp.setString(1, user.getUsername());
		ptmp.setInt(2, user.getAge());
		ptmp.setString(3, user.getSex());
		ptmp.setInt(4, user.getScore());
		ptmp.setString(5, user.getClasses());
		ptmp.setInt(6, user.getId());
		//执行
		ptmp.execute();
	}
	/**
	 *              单一查询
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public User findId(int id) throws Exception{
		//获得连接对象
		Connection conn=DBUtil.getConnection();
		//拼写sql语句
		String sql="select * from user where id=?";
		//预编译
		CallableStatement ptmp = conn.prepareCall(sql);
		//设置参数
		ptmp.setInt(1,id);
		//接收结果集
		ResultSet rs = ptmp.executeQuery();
		User user=null;
		while(rs.next()){
			//拿到需要的数据
			String username =rs.getString("username");
			Integer age=rs.getInt("age");
			String sex=rs.getString("sex");
			Integer score=rs.getInt("score");
			String classes=rs.getString("classes");
			//创建对象，传参
			user=new User(id,username, age, sex, score, classes);
		}
		return user;
	}
	/**
	 *               查询所有，显示所有
	 * @return
	 * @throws Exception
	 */
	public List<User> queryUserList() throws Exception{
		//创建集合
		List<User> list = new ArrayList<User>();
		User user=null;
		//查询数据库数据获得连接
		Connection conn=DBUtil.getConnection();
		//拿到链接操作对象
		Statement stmp = conn.createStatement();
		//定义SQL语句
		String sql="select * from user";
		//通过对象执行sql语句，拿到结果  （拿到的是一个结果集）
		ResultSet rs = stmp.executeQuery(sql);
		//拿到结果集后，进行数据操作，数据处理  while循环
		while(rs.next()){
			//拿到数据
			Integer id=rs.getInt("id");
			String username = rs.getString("username");
			Integer age = rs.getInt("age");
			String sex=rs.getString("sex");
			Integer score=rs.getInt("score");
			String classes=rs.getString("classes");
			//创建一个实体类对象  传入拿到的每一个数据
			user=new User(id, username, age, sex, score, classes);
			//添加进集合
			list.add(user);
		}
		//返回集合
		return list;
	}
	
	public boolean isExist(int id) throws Exception{
		
		this.setFlager(false);
		
		//创建集合
		List<User> list = new ArrayList<User>();
		User user=null;
		//查询数据库数据获得连接
		Connection conn=DBUtil.getConnection();
		//拿到链接操作对象
		Statement stmp = conn.createStatement();
		//定义SQL语句
		String sql="select * from user";
		//通过对象执行sql语句，拿到结果  （拿到的是一个结果集）
		ResultSet rs = stmp.executeQuery(sql);
		//拿到结果集后，进行数据操作，数据处理  while循环
		while(rs.next()){
			//拿到数据
			Integer id2=rs.getInt("id");
			String username = rs.getString("username");
			Integer age = rs.getInt("age");
			String sex=rs.getString("sex");
			Integer score=rs.getInt("score");
			String classes=rs.getString("classes");
			//创建一个实体类对象  传入拿到的每一个数据
			user=new User(id2, username, age, sex, score, classes);
			//添加进集合
			list.add(user);
		}
		//返回
		for (User user2 : list) {
			if(id==user2.getId()){
				this.setFlager(true);
			}
		}
		
		
		return this.isFlager();
	}
}

package com.feicui.www_test;

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

import com.feicui.www_dao.UserDao;
import com.feicui.www_entity.User;



public class StudentsDemo {
   static Scanner sc = new Scanner(System.in);
	public static void main(String[] args) throws Exception {
		String isGoOn="0";//是否继续，0继续，1或其他字符不继续
		String choice;//选择
		boolean i=true;
		UserDao ud=new UserDao();
		//1、添加学生\n2、删除学生\n3、修改学生信息\n4、显示所有学生信息\n5、查询学生信息\n6、退出
		while(isGoOn.equals("0")){
			show();
			choice=sc.next();
			switch(choice){
			case "1":
				System.out.println("请输入学生姓名：");
				String username=sc.next();
				System.out.println("请输入学生年龄：");
				Integer age=sc.nextInt();
				System.out.println("请输入学生性别：");
				String sex=sc.next();
				System.out.println("请输入学生分数：");
				Integer score=sc.nextInt();
				System.out.println("请输入学生班级：");
				String classes=sc.next();
				User user=new User(username, age, sex, score, classes);
				ud.add(user);
				System.out.println("保存数据，请稍后~");
				for (int i1 = 0; i1 < 6; i1++) {
					Thread.sleep(300);
					System.out.print("·");
				}
				System.out.println();
				System.out.println("保存成功！");
				break;
			case "2":
				System.out.println("请输入要删除学生的id：");
				int id=sc.nextInt();
				ud.isExist(id);
				if(ud.isFlager()==false){
					System.out.println("查询无果，该学生或不存在！");
				}else{
					System.out.println("已匹配，请稍后~");
					for (int i1 = 0; i1 < 6; i1++) {
						Thread.sleep(300);
						System.out.print("·");
					}
					ud.delete(id);
					System.out.println();
					System.out.println("删除成功");
				}
				break;
			case "3":
				System.out.println("请输入要修改的学生的id：");
				int id1=sc.nextInt();
				ud.isExist(id1);
				if(ud.isFlager()==false){
					System.out.println("查询无果，该学生或不存在！");
				}else{
					System.out.println("已匹配，请稍后~");
					for (int i1 = 0; i1 < 6; i1++) {
						Thread.sleep(300);
						System.out.print("·");
					}
					System.out.println();
						System.out.println("请输入学生姓名：");
						String username1=sc.next();
						System.out.println("请输入学生年龄：");
						Integer age1=sc.nextInt();
						System.out.println("请输入学生性别：");
						String sex1=sc.next();
						System.out.println("请输入学生分数：");
						Integer score1=sc.nextInt();
						System.out.println("请输入学生班级：");
						String classes1=sc.next();
						User user1 =new User(username1, age1, sex1, score1, classes1);
						ud.update(user1);
						System.out.println("保存数据，请稍后~");
						for (int i1 = 0; i1 < 6; i1++) {
							Thread.sleep(300);
							System.out.print("·");
						}
						System.out.println();
						System.out.println("保存成功！");
					}
				break;
			case "4":
				System.out.println("全部学生信息如下：");
				List<User> list =ud.queryUserList();
				for (User user1 : list) {
					System.out.println(user1);
				}
				break;
			case "5":
				System.out.println("请输入学生id查询：");
				int id2=sc.nextInt();
				ud.isExist(id2);
				if(ud.isFlager()==false){
					System.out.println("查询无果，该学生或不存在！");
				}else{
					System.out.println("已匹配，请稍后~");
					for (int i1 = 0; i1 < 6; i1++) {
						Thread.sleep(300);
						System.out.print("·");
					}
					System.out.println();
					User user2=ud.findId(id2);
					System.out.println(user2.toString());
				}
				break;
			case "6":
				isGoOn="1";
				i=false;
				System.out.println("程序退出！");
				break;
			}
		if(i){	
		System.out.println("是否继续？0继续：");
		isGoOn=sc.next();
		}
		}
	}
	/**
	 * 菜单
	 */
	public static void show(){
		System.out.println("********欢迎使用学生管理系统********");
		System.out.println("1、添加学生\n2、删除学生\n3、修改学生信息\n4、显示所有学生信息\n5、查询学生信息\n6、退出");
		System.out.println("请输入你的选择：");
	}
}