Java连接数据库操作

Java连接MySQL的操作

1. 代码如下

• Test类

package Test;

/**
 * 1.通过输入数字来判断进行那种操作
 * 2.紧接着再输入sql语句，执行操作
 */

import Util.MysqlUtil;
import java.sql.SQLException;
import java.util.Scanner;

public class Test {
    private String sql;//the sql that would be executed
    private int command;//the command
    private static final String hint[]={"exit","describe the tables","select all data from tables","insert into tables",
            "delete data from tables","Please select the type of operation"};//the hint of input
    public static void main(String []args) throws SQLException, ClassNotFoundException {
        Test test = new Test();
        MysqlUtil mysqlUtil = new MysqlUtil();
        test.printHint();
        do {
            test.command = test.getCommand();//get command
            switch(test.command){
                case 0:System.out.println(hint[test.command]);
                        return;
                case 1:System.out.println(hint[test.command]);
                        test.sql = test.getSql();
                        mysqlUtil.printTableStructure(mysqlUtil.getConnect(),test.sql);break;
                case 2:System.out.println(hint[test.command]);
                        test.sql = test.getSql();
                        mysqlUtil.doQuerySQL(mysqlUtil.getConnect(),test.sql);break;
                case 3:System.out.println(hint[test.command]);
                        test.sql = test.getSql();
                        mysqlUtil.deleteSQL(mysqlUtil.getConnect(),test.sql);break;
            }
        }while(test.getCommand()!=-1);
    }

    public void printHint(){
        int count = 0;
        for(String s:hint){
            System.out.println(count+"."+s);
            count++;
        }
    }

    public String getSql(){
        Scanner scanner = new Scanner(System.in);//input the commad
        System.out.println("请输入SQL语句");
        String sql = scanner.nextLine();
        return sql;
    }


    public int getCommand(){
        Scanner scanner = new Scanner(System.in);//input the commad
        int command = scanner.nextInt();
        return command;
    }

    public void setCommand(Integer command){
        this.command = command;
    }
}


//test.printHint();//print the hint
//test.setCommand(test.getInput());//get the type of operation
//未完成的功能 -->  通过选项直接修改
//        switch (test.command){
//            case 1:return;
//            case 2:test.opereate(test.command);break;
//            case 3:
//
}

• MysqlUtil类

package Util;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
 * 1.本类提供MySQL连接工具
 * 2.可能Util包和sql包中有重复类导致失败
 */
public class MysqlUtil {
    private String url = "jdbc:mysql://localhost:3306/mydatabase";
    private String user="root";//username is root
    private String password="";//password is null

    public Statement getConnect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//1.通过反射，加载Driver类
        Connection connection = DriverManager.getConnection(url,user,password);//2获得数据库的连接
        Statement statement = connection.createStatement();//通过数据库的连接操作数据库
        return statement;//返回操作状态
    }

    //1.execute Query
    public void doQuerySQL(Statement statement,String sql) throws SQLException {
//        String sql = "select * from course"; -->执行效率不高
        ResultSet result = statement.executeQuery(sql);//执行数据库查询操作
        while(result.next()){
            System.out.println(result.getString("cno")+" "
                    +result.getString("cname")+" "
                    +result.getString("tno")+" ");
        }
    }

    //2.delete
    public void deleteSQL(Statement statement,String sql)  {
        boolean isCompletion = false;
        try {
            isCompletion = statement.execute(sql);
            if(isCompletion){
                System.out.println("The SQL has been execute successfull!");
            }//else enter the printStackTrace()
        }catch
        (SQLException e) {
            e.printStackTrace();
        }
    }

    //3.print the information of current table
    public  void printTableStructure(Statement statement, String sql) throws SQLException, ClassNotFoundException {
        ResultSet result = statement.executeQuery(sql);//执行数据库查询操作
        while(result.next()){
            System.out.println(result.getString("Field")+" "
                    +result.getString("Type")+" "
                    +result.getString("key")+" ");
        }
        //Field | Type        | Null | Key | Default | Extra
    }
}