使用Java实现MySQL数据库列表数据的增删改查---学习进度分享

我是CGBTN2110 的学员,进行了第二阶段两周的学习,学的有MySQL的基本操作和JDBC部分,周末复习突发奇想,向在JAVA里完成MySQL的增删改查操作,经过快一整天的奋斗,成功完成小小的项目,对现阶段的我还是比较大成功感的,特发此文以纪念.

package cn.tedu.jdbc.cn.tedu;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Scanner;

public class Testdatabases {
    @Test
    public void TestDatabasesMain() {
        String sql = null;
        Connection c = null;

        try {

            while (true) {
                System.out.println("请输入需要进行的操作:1.增加表 2.查询所有表 3.查看表数据 4.修改表数据 5.删除表 6 添加表数据 0.退出 ");
                int choose1 = new Scanner(System.in).nextInt();
                switch (choose1) {
                    case 1:
                        System.out.println("进行增加表操作");
                        SQLDML.creaseTable();
                        break;
                    case 2:
                        System.out.println("进行查询所有表");
                        SQLDML.showTables();
                        break;
                    case 3:
                        System.out.println("进行查看表数据");
                        SQLDML.selectTable();
                        break;
                    case 4:
                        System.out.println("进行修改表数据");
                        SQLDML.updateTable();
                        break;
                    case 5:
                        System.out.println("进行删除表数据");
                        SQLDML.dorpTable();
                        break;
                    case 6:
                        System.out.println("进行添加表数据");
                        SQLDML.insertTable();
                        break;

                    case 0:
                        break;
                }
                System.out.println("请问继续使用吗，1继续，2退出");
                int choise = new Scanner(System.in).nextInt();
                if (choise == 2) {
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package cn.tedu.jdbc.cn.tedu;

import javax.jws.soap.SOAPBinding;
import java.sql.*;
import java.util.Scanner;

public class SQLDML {
    static Statement s = null;
    static String sql = null;
     static Connection c=null;
    public static void creaseTable() {
        Connection c =null;
        PreparedStatement ps =null;
        try {
        c = SQLDML.getCon();

        String columnType = null;
        String tableSQL = " ";
        System.out.println("请输入要创建的表名");
        String tableName = new Scanner(System.in).nextLine();
        System.out.println("请输入要创建的列数");
        int columnNum = new Scanner(System.in).nextInt();
        for (int i = 0; i < columnNum; i++) {
            System.out.println("请输入列名");
            String columnName = new Scanner(System.in).nextLine();
            System.out.println("请输入列类型:1.整数 2.小数 3.字符 4.日期");
            int a = new Scanner(System.in).nextInt();
            switch (a) {
                case 1:
                    columnType = "int";break;
                case 2:
                    columnType = "decimal(5,2)";break;
                case 3:
                    columnType = "varchar(12)";break;
                case 4:
                    columnType = "date";break;
            }
            tableSQL = tableSQL + columnName +" "+ columnType + ",";
        }
            tableSQL  = tableSQL .substring(0,tableSQL.length()-1);
        sql = "create table " + tableName + "(" + tableSQL + ")";
            System.out.println(sql);
            ps = c.prepareStatement(sql);
            ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            SQLDML.close(ps,c);
        }
    }
    public static void showTables( ){
        PreparedStatement ps =null;
        ResultSet r =null;
        Connection c =null;
        try {
            c = SQLDML.getCon();
            System.out.println("正在查询所有表数据");
            String sql="show tables";
            ps = c.prepareStatement(sql);
            r = ps.executeQuery();
            while(r.next()){
                System.out.println(r.getString(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(r,ps,c);
        }


    }
    public static void selectTable(){
        PreparedStatement ps =null;
        ResultSet r =null;
        Connection c =null;
        try {
            c = SQLDML.getCon();
            System.out.println("请输入要查看的表名");
            String tableName=new Scanner(System.in).nextLine();
            String sql="select * from "+tableName;
            ps = c.prepareStatement(sql);
            r = ps.executeQuery();
            ResultSetMetaData m = r.getMetaData();
            int cc = m.getColumnCount();
            while(r.next()){
                for (int i = 1; i < cc+1; i++) {
                    System.out.print(r.getString(i)+" "+"\t");
                }
                System.out.println("");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            SQLDML.close(r,ps,c);
        }

    }
    public static void updateTable() {
        PreparedStatement ps =null;
        Connection c =null;
        try {
            c = SQLDML.getCon();

            System.out.println("请输入要修改的表");
            String tableName=new Scanner(System.in).nextLine();
            System.out.println("请输入要修改主键名");
            String mainName=new Scanner(System.in).nextLine();
            System.out.println("请输入要修改主键行数");
            String mainValue=new Scanner(System.in).nextLine();
            System.out.println("请输入要修改的修改行名");
            String name=new Scanner(System.in).nextLine();
            System.out.println("请输入要修改的值");
            String value=new Scanner(System.in).nextLine();
            String sql="update "+tableName+" set "+name+"="+value+" where "+mainName+"="+mainValue;
            ps = c.prepareStatement(sql);

            ps.executeUpdate();
            System.out.println("修改完成");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            SQLDML.close(ps,c);
        }

    }
    public static void dorpTable() {
        PreparedStatement ps =null;
        ResultSet r =null;
        Connection c =null;
        try {
            c = SQLDML.getCon();
            System.out.println("请输入要删除的表");
            String tableName = new Scanner(System.in).nextLine();
            String sql="drop table "+tableName;

            ps = c.prepareStatement(sql);
            ps.executeUpdate();
            System.out.println("已删除");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(r,ps,c);
        }



    }
    public static void insertTable(){
        PreparedStatement ps =null;
        ResultSet r =null;
        Connection c =null;
        String valuess=" ";
        try {
            c = SQLDML.getCon();
            System.out.println("请输入要增加数据的表名");
            String tableName = new Scanner(System.in).nextLine();
            String sql="select * from "+tableName;
            ps = c.prepareStatement(sql);
            r = ps.executeQuery();
            ResultSetMetaData m = r.getMetaData();
            int cc = m.getColumnCount();
            for (int i = 1; i <cc+1; i++) {
                System.out.println("请输入加入的值");
                String word=new Scanner(System.in).nextLine();
                valuess=valuess+word+",";
            }
            valuess  = valuess .substring(0,valuess.length()-1);
            sql="insert into "+tableName+" values("+valuess+")";
            ps = c.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(r,ps,c);
        }



    }


    public static Connection getCon(){
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost:3306/CGB21101?characterEncoding=utf8";
                c = DriverManager.getConnection(url, "root", "root");
            } catch (Exception e) {
                e.printStackTrace();
            }
            return c;


        }


        public static void close(Statement s ,Connection c){
            if (s!=null){
                try {
                    s.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    s=null;
                }
            }
            if (c!=null){
                try {
                    c.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    c=null;
                }
            }

        }
        public static void close(PreparedStatement ps ,Connection c){
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                ps=null;
            }
        }
        if (c!=null){
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                c=null;
            }
        }
    }
        public static void close(ResultSet r , PreparedStatement ps , Connection c){
        if (r!=null){
            try {
                r.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                r=null;
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                ps=null;
            }
        }
        if (c!=null){
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                c=null;
            }
        }

    }



}