JAVA笔记-JDBC

执行步骤：

1. 1.java工程下创建文件夹lib,往lib目录下导入jar包( mysql-connector-java-5.1.32.jar）（直接拖拽或拷贝）
2. 2.准备数据库基础数据，新建表并插入数据提供查询

3. 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类连接、获取、处理数据

4.     3.1.注册数据库驱动
5.     3.2.连接数据库
6.     3.3.获取传输器执行sql
7.     3.4.处理返回数据结果
8.     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;//垃圾回收机制自动销毁
            }
        }
    }
    }