package mysql;

import java.sql.*;
import java.util.Scanner;

public class MySQLDemo {

// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
// static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
// static final String DB_URL = "jdbc:mysql://localhost:3306/数据库表名";

// MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/java_subject?useSSL=false&serverTimezone=GMT";

// 数据库的用户名与密码,需要根据自己的设置
static final String USER = "root";
static final String PASS = "ilvyu.cn";

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try{
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);
        
        // 打开链接
        System.out.print("连接数据库...");
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
        System.out.println("连接数据库成功!");
    
        // 创建表
        System.out.println("判断数据库是否存在表 Product");
        stmt = conn.createStatement();
        ResultSet rs1 =conn.getMetaData().getTables(null, null, "Product", null);        
        System.out.println("以prepareStatement方法实例化Connection对象...");
        String sql = "create table Product(Product_Code char(7),Description varchar(40),price decimal(10,2))";            
        if(rs1.next()) {                     
            stmt.execute(sql);
        }
        // 数据库增删改查
        String sql1 = "insert into Product values(?,?,?)";
        String sql2 = "delete from Product where Description=?";
        String sql3 = "update Product set price=? where Description=?";
        String sql4 = "select * from Product";
        String sql5 = "select * from Product where price>?";
        String sql6 = "update Product set price= ? where Description=?";
        
        PreparedStatement stmt1 = conn.prepareStatement(sql1);           
        System.out.println("增加数据...");
        stmt1.setString(1,"ilvyu_1");stmt1.setString(2,"钢笔");stmt1.setString(3,"20");
        stmt1.executeUpdate();
        stmt1.setString(1,"ilvyu_2");stmt1.setString(2,"笔记本");stmt1.setString(3,"10");
        stmt1.executeUpdate();
        stmt1.setString(1,"ilvyu_3");stmt1.setString(2,"铅笔");stmt1.setString(3,"5");
        stmt1.executeUpdate();
        stmt1.setString(1,"ilvyu_4");stmt1.setString(2,"Toaster");stmt1.setString(3,"2");
        stmt1.executeUpdate();
        
        stmt1 = conn.prepareStatement(sql2);
        System.out.println("删除数据-铅笔");
        stmt1.setString(1, "铅笔");
        stmt1.executeUpdate();
        
        stmt1 = conn.prepareStatement(sql3);
        System.out.println("更改数据-Toaster-价格改为 5");
        stmt1.setString(1, "5");stmt1.setString(2, "Toaster");
        stmt1.executeUpdate();
        
        stmt1 = conn.prepareStatement(sql4);
        System.out.print("查询全部数据  ");
        ResultSet rs2 = stmt1.executeQuery();
        System.out.println("查询结果如下:");
        while(rs2.next()) {
            System.out.println(rs2.getString(1)+' '+rs2.getString(2)+' '+rs2.getFloat(3));
        }
        
        stmt1 = conn.prepareStatement(sql5);
        System.out.print("输入一个价格,查询大于该价格的所有产品信息:");
        Scanner in =new Scanner(System.in);
        float price = in.nextFloat();
        stmt1.setString(1, String.valueOf(price));
        //String sql8 = "select * from Product where price>'"+price+"'";
        ResultSet rs3 = stmt1.executeQuery();
        System.out.println("查询结果如下:");
        while(rs3.next()) {
            System.out.println(rs3.getString(1)+' '+rs3.getString(2)+' '+rs3.getFloat(3));
        }
        
        stmt1 = conn.prepareStatement(sql6);
        System.out.print("输入一个价格,更新产品’Toaster‘的价格信息:");
        price = in.nextFloat();
        stmt1.setString(1, String.valueOf(price));stmt1.setString(2, "Toaster");
        //String sql9 = "update Product set price='"+ price +"' where Description='Toaster' ";
        stmt1.executeUpdate();

        // 完成后关闭
        rs1.close();
        rs2.close();
        rs3.close();
        stmt.close();
        conn.close();
    }catch(SQLException se){
        // 处理 JDBC 错误
        se.printStackTrace();
    }catch(Exception e){
        // 处理 Class.forName 错误
        e.printStackTrace();
    }finally{
        // 关闭资源
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException se2) {
            // 什么都不做
        } 
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
    System.out.println("Goodbye!");
}
}

 package mysql;

 import java.sql.*;

 public class MySQLDemo {

// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
// static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
// static final String DB_URL = "jdbc:mysql://localhost:3306/数据库表名";

// MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/java_subject?useSSL=false&serverTimezone=GMT";

// 数据库的用户名与密码,需要根据自己的设置
static final String USER = "root";
static final String PASS = "ilvyu.cn";

@SuppressWarnings("resource")
public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement stmt1 = null;
    Savepoint sp = null;
    try{
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);           
        // 打开链接
        System.out.print("连接数据库...");
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
        System.out.println("连接数据库成功!");        
        //事务状态处理
        if(conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
        // 创建表            
        System.out.print("判断数据库是否存在表 Product...");           
        ResultSet rs1 =conn.getMetaData().getTables(null, null, "Product", null);            
        String sql = "create table Product(Product_Code char(7),Description varchar(40),price decimal(10,2) CHECK(price>=0.0))";
        stmt1 = conn.prepareStatement(sql);
        if(!rs1.next()) {                
            stmt1.execute();
            System.out.println(" 表创建成功");
        }
        else {
            System.out.println(" 表存在");
        }
        // 事务处理
        String sql1 = "insert into Product values(?,?,?)";
        String sql2 = "select * from Product";
        String sql3 = "update Product set price = price-10 where Description = 'Toaster'";
        String sql4 = "update Product set price = price+10 where Description = 'Hair dryer'";
        stmt1 = conn.prepareStatement(sql1);
        System.out.println("批量添加数据...");
        stmt1.setString(1,"ilvyu_1");stmt1.setString(2,"钢笔");stmt1.setString(3,"80");
        stmt1.addBatch();
        stmt1.setString(1,"ilvyu_2");stmt1.setString(2,"笔记本");stmt1.setString(3,"40");
        stmt1.addBatch();
        stmt1.setString(1,"ilvyu_3");stmt1.setString(2,"铅笔");stmt1.setString(3,"20");
        stmt1.addBatch();
        stmt1.setString(1,"ilvyu_4");stmt1.setString(2,"Toaster");stmt1.setString(3,"10");
        stmt1.addBatch();
        stmt1.setString(1,"ilvyu_5");stmt1.setString(2,"Hair dryer");stmt1.setString(3,"5");
        stmt1.addBatch();
        stmt1.executeBatch();
        System.out.println("数据添加完成");    
        //设置还原点
        sp = conn.setSavepoint();
        stmt1 = conn.prepareStatement(sql2);
        System.out.print("查询全部数据  ");
        ResultSet rs2 = stmt1.executeQuery();
        System.out.println("查询结果如下:");
        while(rs2.next()) {
            System.out.println(rs2.getString(1)+' '+rs2.getString(2)+' '+rs2.getFloat(3));
        }            
        System.out.println("更新Toaster的price值减10 ");
        stmt1 = conn.prepareStatement(sql3);
        stmt1.executeUpdate();
        System.out.println("更新Hair dtyer的price值加10 ");
        stmt1 = conn.prepareStatement(sql4);
        stmt1.executeUpdate();
        conn.commit();
        System.out.println("事务处理完毕");
        conn.setAutoCommit(true);
        // 完成后关闭
        rs1.close();
        rs2.close();
        stmt1.close();
        conn.close();
    }catch(SQLException se){
        // 处理 JDBC 错误
        se.printStackTrace();
    }catch(Exception e){
        // 处理 Class.forName 错误
        if(conn!=null) {
            try {
                conn.rollback(sp);
                System.out.println("更新有误,事务回滚");
            }catch(SQLException se2) {
                se2.printStackTrace();
            }
        }
        e.printStackTrace();
    }finally{
        // 关闭资源
        try {
            if (stmt1 != null)
                stmt1.close();
        } catch (SQLException se3) {
            // 什么都不做
        } 
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
    System.out.println("程序执行完毕");
}
}
Last modification:November 7th, 2019 at 10:10 pm