查询&结果集处理
按照下标取值
package com.qf.jdbc;
import java.sql.*;
public class Demo1_select {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
Statement s = conn.createStatement( );
// 执行查询使用executeQuery
String sql = "select sal,hiredate,empno id,ename name from emp";
// 查询返回的是一种虚拟表,用集合装数据.即ResultSet
ResultSet rs = s.executeQuery(sql);
while(rs.next()){
/**
* 取值有两种方式:
* getXxx(),根据数据类型再按以下方式取值
* 1 按照列下标,从1开始,与虚拟表列顺序一致
* 2 按照列名,是虚拟表的名字.即如果有别名就是别名取值
*/
// 列下标取值
int empno = rs.getInt(1);
String ename = rs.getString(2);
double sal = rs.getDouble(3);
Date date = rs.getDate(4);
System.out.println(empno+"-"+ename+"-"+sal+"-"+date);
}
// 结果集关流
rs.close();
s.close();
conn.close();
}
}
按照列名取值(重点)
package com.qf.jdbc;
import java.sql.*;
public class Demo1_select {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
Statement s = conn.createStatement( );
// 执行查询使用executeQuery
String sql = "select sal,hiredate,empno id,ename name from emp";
// 查询返回的是一种虚拟表,用集合装数据.即ResultSet
ResultSet rs = s.executeQuery(sql);
while(rs.next()){
/**
* 取值有两种方式:
* getXxx(),根据数据类型再按以下方式取值
* 1 按照列下标,从1开始,与虚拟表列顺序一致
* 2 按照列名,是虚拟表的名字.即如果有别名就是别名取值
*/
// 列名取值
int empno = rs.getInt("id");
String ename = rs.getString("name");
double sal = rs.getDouble("sal");
Date hiredate = rs.getDate("hiredate");
System.out.println(empno+"-"+ename+"-"+sal+"-"+hiredate);
}
// 结果集关流
rs.close();
s.close();
conn.close();
}
}
我们建议以后就使用列名取值!
登录案例
准备数据表
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`createTime` date DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
package com.qf.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Demo2_Login {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("==== 欢迎登录天地银行 ====" );
System.out.println("请输入用户名: " );
String username = scanner.next( );
System.out.println("请输入密码: " );
String password = scanner.next( );
System.out.print("正在登录" );
for (int i = 1; i < 4; i++) {
Thread.sleep(1000);
System.out.print(". ");
}
System.out.println( );
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
Statement s = conn.createStatement( );
// 登录
String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
ResultSet rs = s.executeQuery(sql);
if(rs.next()) {
int uid = rs.getInt("uid");
String uname = rs.getString("username");
String pwd = rs.getString("password");
Date createTime = rs.getDate("createTime");
System.out.println("登录成功!欢迎"+uname+",您的注册时间是:"+createTime );
} else {
System.out.println("用户名或密码错误!" );
}
rs.close();
s.close();
conn.close();
scanner.close();
}
}
预处理语句(重点)
将之前的处理语句变为预处理语句parameterStatement
package com.qf.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Demo3_LoginPlus {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("==== 欢迎登录天地银行 ====" );
System.out.println("请输入用户名: " );
String username = scanner.nextLine( );
System.out.println("请输入密码: " );
String password = scanner.nextLine( );
System.out.print("正在登录" );
for (int i = 1; i < 4; i++) {
Thread.sleep(1000);
System.out.print(". ");
}
System.out.println( );
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
// Statement是处理语句,它有可能出现SQL注入
// Statement s = conn.createStatement( );
// 登录
String sql = "select * from user where username = ? and password = ?";
/**
* SQL字符串的模板是: 将拼接处改成占位符?
* 预处理语句,会将SQL字符串中的模板处理替换
* 使用ps对象,给?处赋值
*/
PreparedStatement ps = conn.prepareStatement(sql);
// parameterIndex,即?号的位置
ps.setString(1,username);
ps.setString(2,password);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
int uid = rs.getInt("uid");
String uname = rs.getString("username");
String pwd = rs.getString("password");
Date createTime = rs.getDate("createTime");
System.out.println("登录成功!欢迎"+uname+",您的注册时间是:"+createTime );
} else {
System.out.println("用户名或密码错误!" );
}
rs.close();
ps.close();
conn.close();
scanner.close();
}
}
使用预处理语句,完成注册案例
package com.qf.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Demo4_regist {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("==== 欢迎登录天地银行[注册系统] ====" );
System.out.println("请输入用户名: " );
String username = scanner.nextLine( );
System.out.println("请输入密码: " );
String password = scanner.nextLine( );
System.out.print("正在注册..." );
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
// 登录
String sql = "insert into user (username,password,createTime) values (?,?,?)";
/**
* SQL字符串的模板是: 将拼接处改成占位符?
* 预处理语句,会将SQL字符串中的模板处理替换
* 使用ps对象,给?处赋值
*/
PreparedStatement ps = conn.prepareStatement(sql);
// parameterIndex,即?号的位置
ps.setString(1,username);
ps.setString(2,password);
/**
* java.util.Date提供了设置年与日的构造方法
* 但是年需要减去 1900
* 月份是从0-11
*/
ps.setDate(3,new Date(new java.util.Date(90,0,1).getTime()));
int i = ps.executeUpdate( );
if (i > 0) {
System.out.println("注册成功");
}
ps.close();
conn.close();
scanner.close();
}
}
ORM(重点,理解)
Object Relation Mapping 对象关系映射
1 要根据表设计类
package com.qf.model;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private double sal;
private double comm;
private int deptno;
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Emp() {
}
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 Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
2 写查询需求
package com.qf.jdbc;
import com.qf.model.Emp;
import java.sql.*;
import java.util.ArrayList;
public class Demo5_select_orm {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
String sql = "select * from emp";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
// 创建集合,存储所有对象
ArrayList<Emp> emps = new ArrayList<>( );
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
int mgr = rs.getInt("mgr");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
Date hiredate = rs.getDate("hiredate");
String job = rs.getString("job");
// 封装对象
Emp emp = new Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno);
// 添加集合
emps.add(emp);
}
for (Emp emp : emps) {
System.out.println(emp );
}
// 结果集关流
rs.close();
ps.close();
conn.close();
}
}
DBUtil(能用)
package com.qf.util;
import java.sql.*;
public class DBUtil {
private static final String URL = "jdbc:mysql://localhost:3306/java2212?useSSL=false";
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace( );
}
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Statement s,Connection conn){
try {
s.close();
conn.close();
} catch (Exception e) {
e.printStackTrace( );
}
}
public static void closeAll( ResultSet rs, Statement s,Connection conn){
try {
rs.close();
s.close();
conn.close();
} catch (Exception e) {
e.printStackTrace( );
}
}
}
使用DBUtil
package com.qf.jdbc;
import com.qf.model.Emp;
import com.qf.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
public class Demo6_select_orm_dbutil {
public static void main(String[] args) throws Exception {
// 获得连接对象
Connection conn = DBUtil.getConnection( );
String sql = "select * from emp";
PreparedStatement ps = conn.prepareStatement(sql);
//执行sql操作
ResultSet rs = ps.executeQuery();
// 创建集合,存储所有对象
ArrayList<Emp> emps = new ArrayList<>( );
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
int mgr = rs.getInt("mgr");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
Date hiredate = rs.getDate("hiredate");
String job = rs.getString("job");
//创建emp 对象
Emp emp = new Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno);
//把获得的数据存到集合中
emps.add(emp);
}
//遍历集合
for (Emp emp : emps) {
System.out.println(emp );
}
// 结果集关流
DBUtil.closeAll(rs,ps,conn);
}
}