执行步骤:
- 1.java工程下创建文件夹lib,往lib目录下导入jar包( mysql-connector-java-5.1.32.jar)(直接拖拽或拷贝)
- 2.准备数据库基础数据,新建表并插入数据提供查询
-
create database jt_db charset utf8; use jt_db; create table account( id int primary key auto_increment, name varchar(50), money double ); insert into account values(null, 'tom', 1000); insert into account values(null, 'andy', 1000); insert into account values(null, 'tony', 1000);
3.创建java类连接、获取、处理数据
- 3.1.注册数据库驱动
- 3.2.连接数据库
- 3.3.获取传输器执行sql
- 3.4.处理返回数据结果
- 3.5.释放资源 注:释放顺序:先释放最晚获取到的对象
import java.sql.*;
/*JDBC入门案例*/
public class TestJDBC2 {
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
Statement stat = null;
try {
/*1.注册数据驱动com.mysql.jdbc.Driver 驱动资源
Class.forName获取字节码对象,仅为了加载Driver类*/
Class.forName("com.mysql.jdbc.Driver");
/*2.获取数据库连接,ip+端口号+数据库+编码,
如果连接的数据库服务器时本机ip和默认端口号,ip和端口号可省略*/
String url = "jdbc:mysql://localhost:3306/db2?characterEncoding=utf-8";
String name = "root";//用户名
String password = "qwe123";//密码
conn = DriverManager.getConnection(url, name, password);
/*3.获取传输器:将sql发送给数据库的服务器,数据库服务
器执行完之后返回数据*/
stat = conn.createStatement();
//4.发送sql到服务器并执行sql
String sql = "select * from account";
rs = stat.executeQuery(sql);
// 5.处理结果
while (rs.next()) {
int id = rs.getInt("id");
String name1 = rs.getString("name");
double money = rs.getDouble("money");
System.out.println("id:" + id + " name:" + name1 + " money:" + money);
}
}catch (Exception e){
e.printStackTrace();
}finally {
// 6.释放资源:越晚获取的对象,越先释放
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
rs = null;
}
try {
stat.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
stat = null;
}
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
conn = null;//垃圾回收机制自动销毁
}
}
}
}
数据库基础数据准备
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
PASSWORD VARCHAR(50)
);
INSERT INTO USER VALUES(NULL,'张三','123');
INSERT INTO USER VALUES(NULL,'李四','234');
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/*使用preparedstatement传输器模拟用户登录*/
public class LoginUserStatement {
public static void main(String[] args) {
System.out.println("欢迎进入我的系统!请输入你的用户名");
String username = new Scanner(System.in).nextLine();
System.out.println("请输入密码");
String password = new Scanner(System.in).nextLine();
LoginSystem(username,password);
}
private static void LoginSystem(String uname, String pwd) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try{
conn = utils.getConn();
String sql = "SELECT * FROM USER WHERE username =? AND PASSWORD =?; ";
ps =conn.prepareStatement(sql);
//给第一个sql第一个问好传入uname
ps.setString(1,uname) ;
//给sql第二个问号传值pwd
ps.setString(2,pwd) ;
rs = ps.executeQuery();
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
}catch ( Exception e){
e.printStackTrace();
}finally {
utils.close(conn,rs,ps);
}
}
}
import java.sql.*;
public class utils {
//1.注册驱动,连接数据库
public static Connection getConn() throws Exception {
String url = "jdbc:mysql://localhost:3306/db2?characterEncoding=utf-8";
String name = "root";//用户名
String password = "qwe123";//密码
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, name, password);
return conn;
}
public static void close(Connection conn,ResultSet rs,Statement stat ){
// 6.释放资源:越晚获取的对象,越先释放
if( rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if( stat != null) {
try {
stat.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if( conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
conn = null;//垃圾回收机制自动销毁
}
}
}
}