JDBC的查询
1.查询(根据下表取值,根据列名取值)
固定内容
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2212?useSSL=false","root","123456");
Statement s = conn.createStatement( );
1.1根据下标取值
package com.qfedu.wb;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo4 {
public static void main(String[] args) throws Exception {
//利用下标取值
Connection conn = DBUtil.getConnection();
String sql = "select * from emp ";
Statement s = conn.createStatement();
ResultSet res = s.executeQuery(sql);
while (res.next()) {
//此时下标位置是固定的,所以内容不能随意更换
//易导致字段类型不匹配
int empno = res.getInt(1);
String ename = res.getString(2);
String job = res.getString(3);
int mgr = res.getInt(4);
Date hiredate = res.getDate(5);
double sal = res.getDouble(6);
double comm = res.getDouble(7);
int deptno = res.getInt(8);
System.out.println(empno + ename +job + mgr + hiredate + sal + comm + deptno);
}
DBUtil.CloseAll(conn,res);
}
}
1.2根据列名取值
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo5 {
public static void main(String[] args) throws Exception {
//利用列名取值
Connection conn = DBUtil.getConnection();
String sql = "select * from emp ";
Statement s = conn.createStatement();
ResultSet res = s.executeQuery(sql);
while (res.next()) {
//根据字段获取值,位置可任意
int empno = res.getInt("empno");
String ename = res.getString("ename");
String job = res.getString("job");
int mgr = res.getInt("mgr");
Date hiredate = res.getDate("hiredate");
double sal = res.getDouble("sal");
double comm = res.getDouble("comm");
int deptno = res.getInt("deptno");
System.out.println(empno + ename +job + mgr + hiredate + sal + comm + deptno);
}
//调用关闭流的方法
DBUtil.CloseAll(conn,res);
}
}
2.登录
import java.sql.*;
import java.util.Scanner;
public class Demo3 {
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 = 0; 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/java","root","root");
//获取sql对象语句
String sql = "select * from user where username = ? and password = ?";
//对sql进行预处理
PreparedStatement s = conn.prepareStatement(sql);
s.setString(1,username);
s.setString(2,password);
//此时数据在prepareStatement中
ResultSet res = s.executeQuery();
//遍历出数据
if (res.next()) {
String us = res.getString("username");
String ps = res.getString("password");
System.out.println("登录成功,欢迎 " + us );
} else {
System.out.println("您输入的账户名或者密码有误");
}
//关闭流
res.close();
s.close();
conn.close();
// boolean qs = false;
// while (res.next()) {
// qs = true;
// String us = res.getString("username");
// String ps = res.getString("password");
// //System.out.println(us +"-"+ ps);
// }
// if (qs == true) {
// System.out.println("登录成功,欢迎 " + username );
// } else {
//System.out.println("您输入的有误");
// }
}
}
注意:SQL注入
处理方式:预处理
import java.sql.*;
import java.util.Scanner;
public class Demo6 {
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 = 0; 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/java","root","root");
//获取sql对象语句
//String sql = "select * from user where username = ? and password = ?";
String sql = "select * from user where username = '"+username+"' and password = '"+ password+"'";
//对sql进行预处理
// PreparedStatement s = conn.prepareStatement(sql);
// s.setString(1,username);
// s.setString(2,password);
//此时数据在prepareStatement中
//不进行处理的创建对象
Statement s = conn.createStatement();
//执行
ResultSet res = s.executeQuery(sql);
//遍历出数据
if (res.next()) {
String us = res.getString("username");
String ps = res.getString("password");
System.out.println("登录成功,欢迎 " + us );
} else {
System.out.println("您输入的账户名或者密码有误");
}
//关闭流
// res.close();
s.close();
conn.close();
3.ORM (重点)
我们要作为对象存入到集合中
需要先创建对象
import java.sql.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date date;
private double sal;
private double comm;
private int deptno;
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", date=" + date +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
public Emp() {
}
public Emp(int empno, String ename, String job, int mgr, Date date, double sal, double comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.date = date;
this.sal = sal;
this.comm = comm;
this.deptno = 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 Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
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;
}
}
获取数据库中的数据付给对象并存入到集合中
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class Demo7 {
public static void main(String[] args) throws Exception {
Connection conn = DBUtil.getConnection();
String sql = "select * from emp";
Statement s = conn.createStatement();
ResultSet res = s.executeQuery(sql);
ArrayList<Emp> emp = new ArrayList<>();
while (res.next()) {
int empno = res.getInt("empno");
String ename = res.getString("ename");
String job = res.getString("job");
int mgr = res.getInt("mgr");
Date hiredate = res.getDate("hiredate");
double sal = res.getDouble("sal");
double comm = res.getDouble("comm");
int deptno = res.getInt("deptno");
emp.add(new Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno));
}
//System.out.println(emp);
for (Emp emp1 : emp) {
System.out.println(emp1);
}
}
}
4.DBUtil(封装的方法)
import java.sql.*;
public class DBUtil {
//防止被修改
public static final String URL = "jdbc:mysql://localhost:3306/java?useSSL=false";
public static final String USERNAME = "root";
public static final String PASSWORD = "root";
//自动执行
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 (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭流
public static void CloseAll(Connection conn, ResultSet qs){
try {
conn.close();
qs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void CloseAll(Connection conn, ResultSet qs, PreparedStatement s){
try {
conn.close();
qs.close();
s.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
{
conn.close();
qs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void CloseAll(Connection conn, ResultSet qs, PreparedStatement s){
try {
conn.close();
qs.close();
s.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}