JDBC操作数据库（简单篇）

1.创建公共数据库连接

    1）创建jdbc.properties文件，

    2）格式：

    

#Mysql

driver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/mysql
user=root
password=root

#Oracle
#driver=oracle.jdbc.driver.OracleDriver
#jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#pasword=root

#SQLserver
#driver=
#jdbcUrl=jdbc:microsoft:sqlserver//localhost:1433; DatabaseName=sid
#user=scott
#pasword=root

    2）//创建数据库连接   

    

private Connection getConnection() throws Exception {
        
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties ps = new Properties();
        ps.load(is);
        String driver = ps.getProperty("driver");
        String jdbcUrl = ps.getProperty("jdbcUrl");
        String user = ps.getProperty("user");
        String password = ps.getProperty("password");
        
        Class.forName(driver);
        
        return DriverManager.getConnection(jdbcUrl, user, password);

    }

2.从控制台输入的信息插入到数据库

    1）从控制台输入信息，用Employee对象接收（前提创建一个Employee类）

public Employee getEmployee() {
		Employee employee = new Employee();
		Scanner scanner = new Scanner(System.in);

		System.out.print("empId:");
		int empId = scanner.nextInt();
		employee.setEmpId(empId);

		System.out.print("empName:");
		String empName = scanner.next();
		employee.setEmpName(empName);

		System.out.print("Type:");
		String empType = scanner.next();
		employee.setEmpTyep(empType);

		System.out.print("age:");
		int age = scanner.nextInt();
		employee.setAge(age);

		System.out.print("birth:");
		String birth = scanner.next();
		employee.setBirth(birth);

		return employee;
	}

    2）关闭连接、Statement（更新操作方法用到，避免代码的可读性）

public void closeAll(Statement state,Connection con) {
		if (state != null) {
			try {
				state.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
	}

    3）插入操作方法

public void updateList(Employee e) throws Exception {
		Connection con = null;
		Statement state = null;
		try {
			con = getConnection();
			state = con.createStatement();
			String sql = "INSERT INTO employee (emp_id,emp_name,emp_type,emp_age,birth)" + "VALUES " + " ( " + e.getEmpId()
					+ " , ' " + e.getEmpName() + "','" + e.getEmpTyep() + " ', " + e.getAge() + " ,' " + e.getBirth() +  " ') ";
			state.executeUpdate(sql);
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			closeAll(state, con);
		}

	}

    4）实现从控制台输入信息插入到数据库

public static void main(String[] args) {
		updateTest ut = new updateTest();
		Employee employee = ut.getEmployee();
		try {
			ut.updateList(employee);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

3.在控制台展示要查询的数据库的信息

    1）实现目的：再控制台能通过输入员工编号 或者 员工姓名，查找书相对应的人员信息

    2）实现思路：

            a.创建公共方法，进行数据库连接

            b.对控制台输入的类型做判断，直到输入正确的结果

            c.通过输入的类型返回该sql

            d.通过该sql传入到statement.executeQuery(sql)，并且将获取的每个值传入到Employee构造器里面

            e.打印Employee对象

    3)实现步骤：

        a.获取数据库连接

// 获取数据库连接
	private Connection getConnection() throws Exception {
		InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
		Properties pt = new Properties();
		pt.load(is);
		String driver = pt.getProperty("driver");
		String jdbcUrl = pt.getProperty("jdbcUrl");
		String user = pt.getProperty("user");
		String password = pt.getProperty("password");

		Class.forName(driver);
		Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
		return connection;
	}

        b.从控制台获取想要的查询方式类型并返回该类型

// 从控制台读取一个类型
	private int getInputTyep() {

		System.out.print("请选择输入类型(1.编号  2.姓名) :");
		Scanner sc = new Scanner(System.in);
		int nextInt = sc.nextInt();

		while (true) {
			if (nextInt != 1 && nextInt != 2) {
				System.out.println("输入有误重新输入,请重新输入:");
				nextInt = sc.nextInt();
			} else {
				break;
			}
		}
		return nextInt;

	}

        c.对控制台输入的内容做相对判断

//对控制台类型做判断
    private int getResult() {
        Scanner s = new Scanner(System.in);
        System.out.print("请选择要通过哪个字段查询  1.员工编号 2.员工姓名 ：");
        int nextInt = s.nextInt();
        
        while(true) {
            if(nextInt != 1 && nextInt != 2) {
                System.out.print("选择错误，请重新选择：");
                
            }else {
                break;
            }
        }
        return nextInt;
    }

        d.通过输入的类型返回对应的sql语句

private String returnSql(int type) {
		String sql = "select * from employee where ";
		
		Scanner scanner = new Scanner(System.in);
		if(type == 1) {
			System.out.print("请选择员工编号:");
			int nextInt = scanner.nextInt();
			sql = sql + "emp_id = " + nextInt;
		}else {
			System.out.print("请输入员工姓名：");
			String next = scanner.next();
			sql = sql + "emp_name = " + "'" + next + "'";
		}
		return sql;
	}

        e.通过该sql传入到statement.executeQuery(sql)，并且将获取的每个值传入到Employee构造器里面

private Employee getEmployee(String sql) throws Exception {
		Employee employee = null;
		Connection con = null;
		Statement state = null;
		ResultSet result = null;
		try {
			
			con = getConnection();
			state = con.createStatement();
			result = state.executeQuery(sql);
			while(result.next()) {
				employee =  new Employee(result.getInt(1), result.getString(2),result.getString(3), result.getInt(4),result.getString(5));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeALl(con, state, result);
		}
		return employee;
	}

        f.关闭Connection、Statement、ResultSet

private void closeALl(Connection con,Statement state,ResultSet result) {
		if(result != null) {
			try {
				result.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(state != null) {
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

            g.判断是否查询的编号是否有相关内容

private void result() throws Exception {
		int result = getResult();
		String returnSql = returnSql(result);
		Employee employee = getEmployee(returnSql);
		if(employee == null) {
			System.out.println("查无此人");
		}else {
			System.out.println(employee);
		}
	}

        h.实现查询效果

public static void main(String[] args) {
		try {
			new searchTest().result();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}