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<lineWords.length;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());
	    }
	}


}