概念:JDBC(java database connectivity),就是一种API,提供了规范,定义了接口和类,用于方便操作不同数据库
掌握JDBC的基础就在于四个类
1. DriverManager 驱动类
2. Connection 连接类
3. PreparedStatement 语句类
4. ResultSet 结果类
驱动类:因为市面上有很多的数据库产品,那么相应的就会有许多的驱动,这些都是由数据库厂商提供的
连接类:这是数据库与程序之间的桥梁
语句类:用于编写各种SQL语句来操作数据库
结果类:自然是用来接收返回的数据的
其中重点的方法:
//1. 加载mysql驱动类
Class.forName("com.mysql.jdbc.Driver");
//2. 获得连接对象
/**
'jdbc:mysql://' 为连接mysql固定写法
'localhost:3306/' 表示为连接本机IP的mysql的默认3306端口
'test' 要连接的数据库名
*/
String url = "jdbc:mysql://localhost:3306/test";
String user = "abc";
String password = "123";
Connection conn = DriverManager.getConnection(url, user, password);
//3. 准备SQL语句
/**
sql中的*号可以使用setString()方法被替换成条件被执行
*/
String sql = "SELECT * FROM user WHERE name = ? AND pwd = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
//4. 处理结果
ResultSet rs = ps.executeQuery(); //执行查询语句,接收返回结果
while(rs.next()) { 一笔一笔循环取值,若没有值,返回false
String s = rs.getString(1); //取出当前行中第一个字段
Int i = rs.getInt(2); //取出当前行中第二个字段
...
}
//5. 别忘了关闭资源
rs.close();
ps.close();
conn.close();
贴上代码
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class DBUtils {
private static String driveClass;
private static String url;
private static String user;
private static String password;
static {
url = "jdbc:mysql:///test?useSSL=false";
user = "abc";
password = "123";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
//得到连接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
//关闭资源
public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
if(rs != null) {
try {
rs.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
}
package service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import entity.User;
import util.DBUtils;
public class DoLogin {
public User findUser(String name, String password) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User u = null;
try {
conn = DBUtils.getConnection();
String sql = "SELECT * FROM user WHERE name = ? AND pwd = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2,pwd);
rs = ps.executeQuery();
if(rs.next()) {
u = new User();
u.setName(rs.getString(1));
u.setPassword(rs.getString(2));
}
} catch(SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(rs, ps, conn);
}
return u;
}
}
package client;
import java.util.Scanner;
import entity.User;
import service.DoLogin;
public class Login {
public static void main(String[] args) {
// TODO Auto-generated method stub
System.out.println("请输入用户名");
Scanner scan = new Scanner(System.in);
String name = scan.nextLine();
System.out.println("请输入密码");
String password = scan.nextLine();
DoLogin dl = new DoLogin();
User u = dl.findUser(name, password);
if(u != null) {
System.out.println("欢迎登陆" + " " + u.getName());
} else {
System.out.println("用户名或者密码有误");
}
}
}
package entity;
public class User {
private String name;
private String password;
public User() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [name=" + name + ", password=" + password + "]";
}
}