MySQL实验四数据库的查询_java实验4.(1)查询Mysql数据库

1、编程(QueryStu.java)实现查询操作。输入举例：

>1345        查出具有某些包含输入字符串的记录

SELECT * FROM stu WHERE num LIKE "%1345%" OR name="%1345%" ORDER BY num

> *           查出所有记录

SELECT * FROM stu ORDER BY num

> -88        查出id为88的记录

SELECT * FROM stu WHERE id=88

>exit          退出(空行也退出)

基本思路：先判断输入的字符串是什么，再根据对应的字符串确定不同的mysql语句，然后就执行相应的sql语句。这种代码几乎都是操作数据库的模板，这是查版本的。

import java.util.*;

import java.sql.*;

public class QueryStu {

static private Connection conn;

static int cnt = 0;

static Scanner in = new Scanner(System.in);

static int sno = 1;

public static void main(String args[]) {

if (connect()) {

String lineWords[];

// 是否还有输入

while ((lineWords=getNextLineWords())!=null)

{

String s = "";

for(int i=0;i

s = s + lineWords[i];

}

ResultSet rs;

String ss = s.substring(0,1);

if(ss.equals("*")){

rs = executeQuery("select * from stu;");

}

else if(ss.equals("-")){

s = s.substring(1,s.length());

rs = executeQuery("SELECT * FROM stu WHERE id="+s + ";");

}

else{

rs = executeQuery("SELECT * FROM stu WHERE num LIKE \"%"+s+"%\""+" OR name LIKE \"%"+s+"%\" ORDER BY num;");

}

showStudents(rs);

}

}

else {

System.out.println("Connect Error!");

}

}

public static String[] getNextLineWords(){

if (sno==1){

System.out.println("查询学号和姓名(部分匹配)，以-开头查询id，*查出所有的记录，exit或空行退出.\r\n");

}

System.out.print(sno+"> ");

if (!in.hasNextLine()) { // 是否还有输入

return null;

}

String line = in.nextLine(); // 读取下一行

line = line.trim();

if (line.equals("exit") || line.length() == 0) {

return null;

}

sno++;

return line.split(" ");

}

// 建立连接

private static boolean connect() {

String connectString = "jdbc:mysql://172.18.187.230:3306/teaching"

+ "?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&&useSSL=false";

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(connectString, "user", "123");

return true;

} catch (Exception e) {

System.out.println(e.getMessage());

}

return false;

}

//执行SQL查询语句, 返回结果集

static private ResultSet executeQuery(String sqlSentence) {

Statement stat;

ResultSet rs = null;

try {

stat = conn.createStatement(); //获取执行sql语句的对象

rs = stat.executeQuery(sqlSentence); //执行sql查询，返回结果集

} catch (Exception e) {

System.out.println(e.getMessage());

}

return rs;

}

//显示查询结果

private static void showStudents(ResultSet rs){

try {

int length = 0;

while(rs.next()){

System.out.println(rs.getString("id")+" "+rs.getString("num")+" "+rs.getString("name"));

length++;

}

System.out.println("["+length+"条记录]");

}

catch (Exception e) {

System.out.println(e.getMessage());

}

}

}