Java连接mysql实现select查询

最终的效果如下：

项目如下： （下载链接：http://static.runoob.com/download/mysql-connector-java-5.1.39-bin.jar）

数据库建表语句和插入语句：

create table tb_stusents(
     stuendId int not null,
     schoolName varchar(20),
     name varchar(5),
     age int,
     class varchar(4),
     grade varchar(3),
     subject varchar(3),
     fraction float,
     teacherName varchar(5)
 );
 
 insert into tb_stusents values (1,"南湖一中","张三",17,"高二","1班","语文",100.5,"刘梅");
 
 insert into tb_stusents values (2,"南湖一中","李四",17,"高三","2班","数学",99.5,"刘梅");

Java代码： mysqlconfig.Java：

package mysql;

import java.sql.*;

public class mysqlconfig {
          
   
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
   //数据库IP地址和数据库名称（这里是students）
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/students?characterEncoding=utf8&useSSL=false";

    static final String USER = "root";//用户名
    static final String PASS = "l123456";//密码

    Connection conn=null;

   public Statement excuteSql(){
          
   
       Statement stmt=null;
       try {
          
   
           Class.forName(JDBC_DRIVER);
           // 打开链接
           System.out.println("连接数据库..."+"
");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
           // 实例化查询语句对象
           stmt = conn.createStatement();
       } catch (ClassNotFoundException | SQLException e) {
          
   
           e.printStackTrace();
       }
      return stmt;
    }

    //关闭连接
    public void closeAll(ResultSet resultSet,Statement statement  ){
          
   
        try {
          
   
            resultSet.close();
            statement.close();
            conn.close();
        } catch (SQLException e) {
          
   
            e.printStackTrace();
        }
    }
}

TestSql.java:

import mysql.mysqlconfig;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

public class TestSql {
          
   


    public static void main(String[] args) {
          
   
        Scanner in = new Scanner(System.in);
        System.out.println("请输入select查询语句,无输入直接回车默认查询整表:");
        String input =  in.nextLine();

        mysqlconfig config = new mysqlconfig();//连接数据库
        String sql = "".equals(input) ? "select * from tb_students" : input;//要执行的sql语句
        Statement statement = config.excuteSql();
        try {
          
   
            ResultSet rs = statement.executeQuery(sql);//执行sql并得到结果
            List<Map<String, Object>> list = new ArrayList();//保存每一行的数据
            Set<String> columnSet = new HashSet<>();//保存列名
            ResultSetMetaData md = rs.getMetaData();//获取键名
            int columnCount = md.getColumnCount();//获取列的数量
            while (rs.next()) {
          
   
                Map rowData = new ConcurrentHashMap();//声明Map,保存每一行的数据
                for (int i = 1; i <= columnCount; i++) {
          
   
                    columnSet.add(md.getColumnName(i));
                    rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
                }
                list.add(rowData);
            }
            config.closeAll(rs, statement);//关闭连接
            showResult(list, columnSet);//显示结果
        } catch (SQLException e) {
          
   
            e.printStackTrace();
        }
    }

    //显示结果
    public static void showResult(List<Map<String, Object>> list, Set<String> columnSet) {
          
   
        columnSet.forEach(s -> System.out.print(s + "	" + " "));
        System.out.println();
        System.out.println("--------------------------------------------------------------------------------------------");
        for (Map<String, Object> map : list) {
          
   
            for (Map.Entry<String, Object> m : map.entrySet()) {
          
   
                System.out.print(m.getValue() + "		");
            }
            System.out.println();
        }
    }
}