JDBC--(2)操作MySQL查询数据

MySQL与JAVA 数据类型对照表

执行语句executeQuery(); 返回结果集 ResultSet
表示数据库查询的结果的集合,在执行查询语句时就会得到一个这样的结果
常用方法
boolean next():判断是否有下一行数据,若有,则向下移动一行指针.
getXxx(int columnIndex):获取当前行中,第几列.(从1开始):不推荐
getXxx(String columnName):获取当前行中的,指定列名的列的值.columnName是列名/列的别名
若列的类型是VARCHAR/CHAR/TEXT,都使用getString来获取列的值.
若列的类型是int/integer/–>getInt来获取列的值.

package com.iris.jdbc.dql;

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

public class QueryClass {
	public static void main(String[] args) throws Exception {
		//加载注册驱动
		Class.forName("com.mysql.cj.jdbc.Driver");			//JAVA1.6之后可以不写此语句，根据开发环境类型而定
		//连接数据库
		String url = "jdbc:mysql://localhost:3306/jdbc_db?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";		//url 数据库地址
		String user="root";		//user 数据库用户名
		String password="......."	;	//password 数据库密码
		Connection conn = DriverManager.getConnection(url, user, password);
		
		Statement st = conn.createStatement();

		//释放资源
		test1("SELECT count(*) as total from emp",st);
		test2("SELECT * FROM emp WHERE ename='鲁班'",st);
		test3("SELECT * FROM emp",st);
		st.close();
		conn.close();
	}
	//查询单个结果
	static void test1(String sql,Statement st) throws Exception {
		ResultSet res = st.executeQuery(sql);
		if(res.next()) {
			int count1 = res.getInt(1);
			System.out.println(count1);
			int count2 = res.getInt("total");
			System.out.println(count2);
		}	
	}
	//查询一行结果
	static void test2(String sql,Statement st) throws Exception {
		ResultSet res = st.executeQuery(sql);
		if(res.next()) {
			int empno = res.getInt("empno");
			String ename = res.getString("ename");
			String job = res.getString("job");
			System.out.println("empno = "+empno+"  name = "+ename+"  job = " +job);
		}
	}
	//查询多行结果
	static void test3(String sql,Statement st) throws Exception {
		ResultSet res = st.executeQuery(sql);
		while(res.next()) {
			int empno = res.getInt("empno");
			String ename = res.getString("ename");
			String job = res.getString("job");
			System.out.println("empno = "+empno+"  name = "+ename+"  job = " +job);
		}
	}
}