package JDBC;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo01 {
public List<Emp> findAll() throws SQLException {
ResultSet r = null;
Statement s = null;
Connection root = null;
List<Emp> emp = new ArrayList<>();
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接对象
root = DriverManager.getConnection("jdbc:mysql://localhost/pracice", "root", "123456");
//sql语句
String sql = "select * from user";
//获取执行sql的对象
s = root.createStatement();
//执行sql
r = s.executeQuery(sql);
Emp e = new Emp();
while (r.next()){
int id = r.getInt("id");
String name = r.getString("name");
e.setId(45);
e.setName("xiaom");
emp.add(e);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
if(r != null){
r.close();
}
if(s != null){
r.close();
}
if(root != null){
r.close();
}
return emp;
}
public static void main(String[] args) throws SQLException {
List<Emp> list = new JDBCDemo01().findAll();
System.out.println(list);
}
}
package JDBC;
public class Emp {
private int id;
private String name;
public Emp() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Emp(int id, String name) {
this.id = id;
this.name = name;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
结果:
由于上面的代码重复度过于太高,所以我们将对其进行简写
package util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/*
*
* JDBC工具栏
* */
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String Driver;
/*
* 文件的读取只需要一次就可以拿到这些值,所以我们看呀选择静态代码块来解决
* */
static {
//读取资源文件,获取值
//创建Properties集合类
Properties pro = new Properties();
//加载文件
try {
//静态代码块只能 try catch 处理异常,不能用 throw 抛出异常
//1.可以写绝对路径
//2.获取src路径下的文件--->ClassLoader 类加载器
ClassLoader cL = JDBCUtils.class.getClassLoader();
URL res = cL.getResource("jdbc.properties");
String path = res.getPath();
pro.load(new FileReader(path));
//获取资源
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
/*
//放入注册驱动就不能用
//Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class util.JDBCUtils
Driver = pro.getProperty("Driver");
//注册驱动
Class.forName(Driver);*/
} catch (IOException e) {
e.printStackTrace();
} /*catch (ClassNotFoundException e) {
e.printStackTrace();
}*/
}
//获取连接对象
public static Connection getConnention() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//释放资源
public static void close(Statement s, Connection c){
if(s != null){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(c != null){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//释放资源 方法重载
public static void close(ResultSet r,Statement s, Connection c){
if(s != null){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(c != null){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(r != null){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package JDBC;
import util.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo01 {
public List<Emp> findAll() throws SQLException, ClassNotFoundException {
ResultSet r = null;
Statement s = null;
Connection c = null;
List<Emp> emp = new ArrayList<>();
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
c = JDBCUtils.getConnention();
//sql语句
String sql = "select * from user";
//获取执行sql的对象
s = c.createStatement();
//执行sql
r = s.executeQuery(sql);
Emp e = new Emp();
while (r.next()) {
int id = r.getInt("id");
String name = r.getString("name");
e.setId(45);
e.setName("xiaom");
emp.add(e);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(r,s,c);
}
return emp;
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
List<Emp> list = new JDBCDemo01().findAll();
System.out.println(list);
}
}
url=jdbc:mysql://localhost/pracice
user=root
password=123456
driver=com.mysql.jdbc.Driver
练习:
键盘输入用户名以及密码 判断正确以及错误
注意:
ps.setInt(1,user);
ps.setString(2,password);
r = ps.executeQuery();
setXxx()方法要在executeQuery前面!!!
package JDBC;
import util.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
public class JDBCDemo02 {
public static void main(String[] args) throws ClassNotFoundException {
Connection c = null;
ResultSet r = null;
// Statement s = null;
PreparedStatement ps = null;
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//键盘输入
System.out.println("请输入你的用户名");
Scanner sc = new Scanner(System.in);
int user = sc.nextInt();
System.out.println("请输入你的密码");
Scanner sc1 = new Scanner(System.in);
String password = sc1.nextLine();
if(user == 0 && password == null){
System.out.println("失败");
}
try {
//
c = JDBCUtils.getConnention();
String user_sql = "select id from user";
//获取执行sql的对象
// ps = c.createStatement();
//执行sql
//用prepareStatement可以避免sql注入问题 'a' or 'a' = 'a' 按照逻辑为true
// r = s.executeQuery("select * from user where id=" + user + " and name=" + password );
String sql = "select * from user where id=? and name=?";
ps = c.prepareStatement(sql);
//r = ps.executeQuery();
ps.setInt(1,user);
ps.setString(2,password);
r = ps.executeQuery();
if(r.next()){
System.out.println("成功");
}else{
System.out.println("失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.close(r,ps,c);
}
}
}