Jdbc_查询

package jdbc;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import org.junit.Test;

import day01.Emp;

public class Study01 {

查询

@Test
	public void mytest01() throws SQLException, ClassNotFoundException {
		// 1）载入jdbc驱动(指定我要连接到哪种数据库，连接不同数据库用不同驱动)
		Class.forName("com.mysql.jdbc.Driver");
		// java.sql.
		// 2）定义连接url(连接准备 url,端口，用户名，密码)
		String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
		String user = "root";
		String password = "root";
		// 3）建立连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);

		// 4）创建PreparedStatement（动态） Statement(静态的)(拼sql语句)
		PreparedStatement ps = conn.prepareStatement("select * from emp");
		// 5）执行数据库命令（crud)
		// ps.executeUpdate();//用于insert update delete
		ResultSet rs = ps.executeQuery();// 用于select
		// 6）结果的处理(查询有,添加修改删除没有)
		// rs.next() iterator hasnext()+next()
		while (rs.next()) {
			int no = rs.getInt("empno");
			System.out.println(no);
			String name = rs.getString("ename");
			System.out.println(name);
			System.out.println("-----------");
		}
		// 7）关闭连接
		rs.close();
		ps.close();
		conn.close();
	}

package day01;
/**
 * 数据库的表可以映射成java类
 * 1.类名-表名2.属性名-字段名(类型要匹配)
 *
 */

public class Emp {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private String hiredate;
	private int sal;
	private int comm;
	private int deptno;
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public String getHiredate() {
		return hiredate;
	}
	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public Emp() {
		super();
		
	}
	public Emp(int empno, String ename, String job, int mgr, String hiredate, int sal, int comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}

}

//查询
	@Test
	public void mytest02() throws SQLException, ClassNotFoundException {
		// 1）载入jdbc驱动(指定我要连接到哪种数据库，连接不同数据库用不同驱动)
		Class.forName("com.mysql.jdbc.Driver");
		// java.sql.
		// 2）定义连接url(连接准备 url,端口，用户名，密码)
		String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
		String user = "root";
		String password = "root";
		// 3）建立连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);

		// 4）创建PreparedStatement（动态） Statement(静态的)(拼sql语句)
		PreparedStatement ps = conn.prepareStatement("select * from emp");
		// 5）执行数据库命令（crud)
		// ps.executeUpdate();//用于insert update delete
		ResultSet rs = ps.executeQuery();// 用于select
		// 6）结果的处理(查询有,添加修改删除没有)
		// rs.next() iterator hasnext()+next()
		List<Emp> empList = new ArrayList();// emplist在循环外定义
		while (rs.next()) {
			Emp emp = new Emp();
			// 每读取一条记录,就创建一个对象
			emp.setEmpno(rs.getInt("emp"));
			emp.setEname(rs.getString("ename"));
			emp.setComm(rs.getInt("comm"));
			emp.setDeptno(rs.getInt("deptno"));
			emp.setHiredate(rs.getString("hiredate"));
			emp.setJob(rs.getString("job"));
			emp.setMgr(rs.getInt("mgr"));
			emp.setSal(rs.getInt("sal"));
			empList.add(emp);
		}
		// 7）关闭连接
		System.out.println(empList);
		rs.close();
		ps.close();
		conn.close();
	}

//查询
	@Test
	public void myTest03() throws SQLException, ClassNotFoundException {
		// 1）载入jdbc驱动(指定我要连接到哪种数据库，连接不同数据库用不同驱动)
		Class.forName("com.mysql.jdbc.Driver");
		// java.sql.DriverManager.registerDriver(new Driver());
		// 2）定义连接url(连接准备 url,端口，用户名，密码)
		// jdbc:mysql://ip:port/databasename?characterEncoding=UTF-8&useUnicode=true
		String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
		String user = "root";
		String password = "root";
		// 3）建立连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);
		// 4）创建PreparedStatement（动态） Statement(静态的)(拼sql语句)
		PreparedStatement ps = conn.prepareStatement("select * from emp where empno=?");
		ps.setInt(1, 7369);

		// 5）执行数据库命令（crud)
		// 注意的点
		// ps.executeUpdate() 用于insert update delete
		// 用于select
		ResultSet rs = ps.executeQuery();
		// 6）结果集的处理（查询有，添加修改删除没有）
		// rs.next() iterator hasnext()+next()
		Emp emp = null;// 单条时，只需要返回一个emp对象即可
		if (rs.next()) {
			// 对象在循环里定义
			emp = new Emp();
			// 每读取一条记录，就创建一个对象
			emp.setEmpno(rs.getInt("empno"));
			emp.setEname(rs.getString("ename"));
			emp.setComm(rs.getInt("comm"));// 有可能会异常
			emp.setDeptno(rs.getInt("deptno"));
			emp.setHiredate(rs.getString("hiredate"));
			emp.setJob(rs.getString("job"));
			emp.setMgr(rs.getInt("mgr"));
			emp.setSal(rs.getInt("sal"));

		}
		System.out.println(emp.getEname());
		// 7）关闭连接
		rs.close();
		ps.close();
		conn.close();
	}