Java之JDBC

概述

• java数据库连接技术(Java DataBase Connectivity)，能实现Java程序对各种数据库的访问
• 由一组使用java语言编写的类和接口(JDBC API)组成，它们位于java.sql以及javax.sql

JDBC访问数据库步骤

Statement与PreparedStatement的区别

PrepareStatement接口是Statement接口的子接口，他继承了Statement接口的所有功能。

PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于 Statement 对象。

常用方法

1、boolean execute()

该语句可以是任何种类的 SQL 语句。

2、ResultSet executeQuery()

执行 SQL 查询，并返回该查询生成的 ResultSet 对象。

3、int executeUpdate()

语句必须是一个 SQL 数据操作语言（DML）语句，比如 INSERT、UPDATE 或 DELETE 语句；或者是无返回内容的 SQL 语句，比如 DDL 语句。

1、prepareStatement

int sid=1001;
prepareStatement ps=conn.prepareStatement("select * from Cust where ID=?");
ps.setInt(1, sid);
rs = ps.executeQuery();

2、Statement

int sid=1001;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from Cust where ID="+sid);

ResultSet

int columns = resultSet.getMetaData().getColumnCount();
 
// 显示列，表格的表头
for (int i = 1; i <= columns; i++) {
    System.out.print(m.getColumnName(i) + "\t\t");
}
 
// 显示表格内容
while (res.next()) {
    for (int i = 1; i <= columns; i++) {
        System.out.print(res.getString(i) + "\t\t");
    }
    System.out.println();
}

代码示例

Mysql.java

package cn.kgc.kb11;

import java.sql.*;

/**
 * @Author ZhangPeng
 * @Date 2021/3/5
 * @Description
 */
public class Mysql {
    private Connection conn;
    private Statement stat;
    private String url;
    private String username;
    private String pwd;

    public Mysql(String url, String username, String pwd) {
        this.url = url;
        this.username = username;
        this.pwd = pwd;
    }

    public void connect(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            conn = DriverManager.getConnection(url,username,pwd);
            stat = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ResultSet query(String sql,String param){
        ResultSet ret = null;
        try {
            ret = stat.executeQuery(sql+param);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }

    public boolean update(String sql){
        int num = 0;
        try {
            num = stat.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return num > 0?true : false;
    }

    public void close(){
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

TestMysql.java

package cn.kgc.kb11;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * @Author ZhangPeng
 * @Date 2021/3/5
 * @Description
 */
public class TestMysql {
    public static void showResult(ResultSet ret){
        int col = 0;
        ResultSetMetaData metaData = null;
        try {
            col = ret.getMetaData().getColumnCount();
            metaData = ret.getMetaData();
            for (int i = 1; i <= col; i++) {
                System.out.print(metaData.getColumnName(i) + "\t");
            }
            System.out.println();

            while (ret.next()){
                for (int i = 1; i <= col; i++) {
                    System.out.print(ret.getObject(i) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://192.168.56.12:3306/bank";
        String username = "root";
        String pwd = "ok";
        Mysql mysql = new Mysql(url,username,pwd);
        mysql.connect();
        ResultSet ret =  mysql.query("select * from account","");
        showResult(ret);

        if (mysql.update("update account set cash=cash-10000 where user_name='ICBC'")){
            System.out.println("修改成功!");
        }

        ResultSet ret2 =  mysql.query("select * from account","");
        showResult(ret2);

        mysql.close();

    }
}