查询部分
查詢備忘錄
1.??????????????功能描述
查詢數據庫信息，并打印在查詢結果頁面上，實現頁面清空，上一頁查詢，下一頁查詢，并實現返回主頁面
2.??????????????語言和環境
A、實現語言：
?? Java
B、實現技術：
?? JavaSE、JDBC、Swing
C、環境要求：
? NetBeans 8.0.2，SQLServer2008、jdk-8u20-windows-x64
3.??????????????界面設計
標簽，按鈕，文本框
輸入查詢數據，查詢主頁面，清空文本框內容，返回主頁面或跳轉到查詢結果頁面
顯示查詢結果，通過上一條，下一條實現對數據庫的結果搜索，并實行跳轉到主頁面功能
?
提示查詢結果，數據庫無此匹配信息
?
4.??????????????數據庫設計
數據庫名：XSML
數據庫表名：Table_1
數據庫表內容：
類名：Username ，Id，Title，Content，Memotype，Memotime
數據庫表顯示：
5．運行代碼
1.數據庫連接代碼
package com.dao;
?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/** *
?* @author Samuel*//*數據庫操作類*/
public class Dao {
protected static StringdbClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
???//注意修改數據庫名稱
???protected static String dbUrl = "jdbc:sqlserver://localhost:1434;DatabaseName=XSML";//數據庫用戶名保存在變量dbUser中
???protected static String dbUser = "sa";//數據庫密碼保存在dbPwd中
???protected static String dbPwd = "123456";
???protected static String second = null;
???private static Connection conn = null;
?
???private static Connection getConnection() {
??????? throw newUnsupportedOperationException("Not supported yet."); //To change bodyof generated methods, choose Tools | Templates.
???}
???//Dao類的構造方法
???Dao() {
??????? try {
??????????? if (conn == null) {
??????????????? Class.forName(dbClassName);
??????????????? conn =DriverManager.getConnection(dbUrl, dbUser, dbPwd);
??????????? } else {
??????????????? return;
??????????? }
??????????? System.out.println("conn成功！");//控制臺打印顯示連接成功
??????? } catch (Exception ee) {
??????????? ee.printStackTrace();
??????? }
?
???}
???/*數據庫查詢方法
???* 方法參數：sql查詢語句
???* 返回值：查詢返回的結果集
???*/
???public static ResultSet executeQuery(String sql) {
??????? try {
??????????? if (conn == null) {
??????????????? new Dao();
??????????? }
??????????? //下面一行調用了Statement類的executeQuery(String sql)方法
??????????? //執行給定的 SQL 語句，該語句返回單個 ResultSet 對象，絕大多數是用SELECT語句
??????????? returnconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql);
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????????? return null;
??????? } finally {
??????? }
???}
???/*數據庫更新方法
???* 方法參數：sql更新語句
???* 返回值：一個整數，指示受影響的行數（即更新計數）
???*/
???static int executeUpdate(String sql) {
?
??????? try {
??????????? if (conn == null) {
??????????????? new Dao();
??????????? }
??????????? //下面一行調用了Statement類中的executeUpdate方法
??????????? //用于執行 INSERT、UPDATE 或 DELETE 語句以及 SQL DDL（數據定義語言）語句
??????????? returnconn.createStatement().executeUpdate(sql);
??????? } catch (SQLException e) {
??????????? System.out.println(e.getMessage());
??????????? return -1;
??????? } finally {
??????? }
}
2.構造方法
package com.dao;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
?*
?*@author Administrator
?*/
?publicclass Dao_check {
???private static String Username;
??? privatestatic String ID;
???private static String Title;
???private static String Content;
???private static String Memotype;
???private static String Memotime;
???
???public static String getUsername()
??? {
?????????????????? returnUsername;
??? }
???public static void setUsername(String Username)
??? {
?????????????????? Dao_check.Username= Username;
??? }
???public static String getID()
??? {
?????????????????? returnID;
??? }
???public static void setID(String ID)
??? {
?????????????????? Dao_check.ID= ID;
??? }
???public static String getTitle()
??? {
?????????????????? returnTitle;
??? }
???public static void setTitle(String Title)
??? {
?????????????????? Dao_check.Title= Title;
??? }
???public static String getContent()
??? {
?????????????????? returnContent;
??? }
???public static void setContent(String Content)
??? {
?????????????????? Dao_check.Content= Content;
??? }
???public static String getMemotype()
??? {
?????????????????? returnMemotype;
??? }
???public static void setMemotype(String Memotype)
??? {
?????????????????? Dao_check.Memotype= Memotype;
??? }
???public static String getMemotime()
??? {
?????????????????? returnMemotime;
??? }
???public static void setMemotime(String Memotime)
??? {
?????????????????? Dao_check.Memotime=Memotime;
??? }
???
???public static int rownum()
??? {
???????int i=0;
???????try
???????{?
???????????Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
???????? }
???????catch(ClassNotFoundException e)
???????{??
???????????System.exit(0);
???????? }
???????Connection? con=null;
???????Statement stmt=null;
???????ResultSet?? rs=null;
?
???????try
???????{?
?????????????con=DriverManager.getConnection("jdbc:sqlserver://localhost:1434;DatabaseName=XSML","sa","123456");
????????????stmt=con.createStatement();
???????????if(Username==null)
???????????{??
??????????????? Username="";
??????????????
???????????}
???????????
???????????if(Title==null)
???????????{??
??????????????? Title="";
???????????????
???????????}
??????????
???????????if(Content==null)
???????????{
??????????????? Content="";
???????????????
???????????}
???????????
???????????if(Memotype==null)
???????????{
???????????????Memotype="";
???????????????
???????????}
???????????
???????????if(Memotime==null)
???????????{
??????????????? Memotime="";
??????????????
???????????}
??????????
??????????????rs=stmt.executeQuery("SELECT *?FROM Table_1 where Username like '"+Username+"%' "
??????????????????? + "and Title like'"+Title+"%' and Content like '"+Content+"%' and Memotypelike '"+Memotype+"%' "
??????????????????? + "and Memotime like'"+Memotime+"%'");
???????????
???????????while(rs.next())
???????????{
???????????????
??????????????i=i+1;
??????????????
???????????}
???????????return i;
???????}
???????catch(SQLException e)
???????{?
???????????//e.printStackTrace();?
???????????System.exit(0);
???????? }finally{
???????????try {
??????????????? rs.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????????try {
??????????????? stmt.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE,null, ex);
???????????}
???????????try {
??????????????? con.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????}
???????return -1;
??? }
???
???
?? //在點擊查詢后，彈出窗口中，直接顯示第一條信息
???public static String Check_print()
??? {
???????
???????try
???????{?
???????????Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
???????? }
???????catch(ClassNotFoundException e)
???????{??
???????????System.exit(0);
???????? }
???????Connection? con=null;
???????Statement stmt=null;
???????ResultSet?? rs=null;
???????try
???????{?
?????????????con=DriverManager.getConnection("jdbc:sqlserver://localhost:1434;DatabaseName=XSML","sa","123456");
????????????stmt=con.createStatement();
???????????
//???????????System.out.println(Username);
//???????????System.out.println(Title);
//???????????System.out.println(Content);
//???????????System.out.println(Memotype);
//???????????System.out.println(Memotime);
???????????String str="1";
???????????if(Username==null)
???????????{??
??????????????? Username="";
??????????????? str=str+"+"+Username;
???????????}
???????????else str=str+"+"+Username;
???????????if(Title==null)
???????????{??
??????????????? Title="";
??????????????? str=str+"+ "+Title;
???????????}
???????????else str=str+"+"+Title;
???????????if(Content==null)
???????????{
??????????????? Content="";
???????????????str=str+"+"+Content;
???????????}
???????????else str=str+"+"+Content;
???????????if(Memotype==null)
???????????{
??????????????? Memotype="";
??????????????? str=str+"+"+Memotype;
???????????}
???????????else str=str+"+"+Memotype;
???????????if(Memotime==null)
???????????{
??????????????? Memotime="";
??????????????? str=str+"+"+Memotime+"+1";
???????????}
???????????else str=str+"+"+Memotime+"+1";
??????????????rs=stmt.executeQuery("SELECT *?FROM Table_1 where Username like '"+Username+"%' "
??????????????????? + "and Title like'"+Title+"%' and Content like '"+Content+"%' and Memotypelike '"+Memotype+"%' "
??????????????????? + "and Memotime like'"+Memotime+"%'");
???????????
??????????
??????????????? rs.next();
???????????
????? ??????Username=rs.getString("Username");????????????
???????????Title=rs.getString("Title");
???????????Content=rs.getString("Content");
???????????Memotype=rs.getString("Memotype");
???????????Memotime=rs.getString("Memotime");
???????????
//???????????System.out.println(Username);
//???????????System.out.println(Title);
//???????????System.out.println(Content);
//???????????System.out.println(Memotype);
//???????????System.out.println(Memotime);
???????????
???????????
???????????return str;
???????}
???????catch(SQLException e)
???????{?
???????????//e.printStackTrace();?
???????????System.exit(0);
???????? }finally{
???????????try {
??????????????? rs.close();
???????????} catch (SQLException ex) {
??????????????? Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE,null, ex);
???????????}
???????????try {
??????????????? stmt.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
? ??????????try {
??????????????? con.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????}
???????return "##";
??? }
???
???
???public static int Check_next(int?i,String str)
??? {
???????
???????try
???????{?
???????????Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
???????? }
???????catch(ClassNotFoundException e)
???????{??
???????????System.exit(0);
???????? }
???????Connection? con=null;
???????Statement stmt=null;
???????ResultSet?? rs=null;
???????try
???????{?
?????????????con=DriverManager.getConnection("jdbc:sqlserver://localhost:1434;DatabaseName=XSML","sa","123456");
????????????stmt=con.createStatement();
???????????
//???????????System.out.println(Username);
//???????????System.out.println(Title);
//???????????System.out.println(Content);
//???????????System.out.println(Memotype);
//???????????System.out.println(Memotime);
???????????int[] a=new int[6];
??? ????????a[0]=0;
???????????String[] ss=new String[10];
???????????ss=str.split("\\+");
???????????if(ss[1].equals(Username))
??????????????? a[1]=1;
???????????else Username="";
???????????if(ss[2].equals(Title))
??????????????? a[2]=2;
???????????else Title="";
???????????if(ss[3].equals(Content))
??????????????? a[3]=3;
???????????else Content="";
???????????if(ss[4].equals(Memotype))
??????????????? a[4]=4;
???????????else Memotype="";
???????????if(ss[5].equals(Memotime))
??????????????? a[5]=5;
???????????else Memotime="";
?????
??????????????rs=stmt.executeQuery("SELECT *?FROM Table_1 where Username like '"+Username+"%' "
??????????????????? + "and Title like'"+Title+"%' and Content like '"+Content+"%' and Memotypelike '"+Memotype+"%' "
??????????????????? + "and Memotime like'"+Memotime+"%'");
???????????
??????????for(int j=0;j<i;j++)
??????????{???
??????????????rs.next();
??????????}
???????????
???????????Username=rs.getString("Username");
???????????Title=rs.getString("Title");
???????????Content=rs.getString("Content");
???????????Memotype=rs.getString("Memotype");
???????????Memotime=rs.getString("Memotime");
???????????
//???????????System.out.println(Username);
//???????????System.out.println(Title);
//???????????System.out.println(Content);
//???????????System.out.println(Memotype);
//???????????System.out.println(Memotime);
???????????
???????????
???????????return 0;
???????}
???????catch(SQLException e)
???????{?
???????????//e.printStackTrace();?
???????????System.exit(0);
???????? }finally{
???????????try {
??????????????? rs.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????????try {
??????????????? stmt.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????????try {
??????????????? con.close();
???????????} catch (SQLException ex) {
???????????????Logger.getLogger(Dao_check.class.getName()).log(Level.SEVERE, null, ex);
???????????}
???????}
???????return -1;
??? }
???
???
???
}
3.調用方法，實現查詢，跳轉，上一頁，下一頁，返回，清空等功能的實現
//查詢：
private voidjButton1_QueryActionPerformed(java.awt.event.ActionEvent evt) {??????????????????????????????????????????????
???String str = jTextField1_Username.getText();
??? inta = str.length();
???????if(a<1){
???????????System.out.println("success");
???????????JOptionPane.showMessageDialog(null,"查詢失敗，用戶不存在","",JOptionPane.INFORMATION_MESSAGE);}???????
???????else{
???????????
???????????Dao_check.setUsername(jTextField1_Username.getText());
???????????Dao_check.setMemotype(jTextField2_Memotype.getText());
???????????Dao_check.setMemotime(jTextField3_MemoTime.getText());
???????????Dao_check.setTitle(jTextField4_Title.getText());
???????????Dao_check.setContent(jTextField5_Content.getText())
;
???????????CheckedJFrame1 frame=new CheckedJFrame1();
???????????frame.setVisible(true);
???????????this.setVisible(false);}// TODO add your handling code here:
?
}
//清空??
private voidjButton2_DeleteActionPerformed(java.awt.event.ActionEvent evt) {???????????????????????????????????????????????
????? this.jTextField1_Username.setText(null);
????? this.jTextField2_Memotype.setText(null);
????? this.jTextField3_MemoTime.setText(null);
????? this.jTextField4_Title.setText(null);
// TODO add your handling codehere:
???}
//上一條
?
???private void jButton1_UpActionPerformed(java.awt.event.ActionEvent evt){???????????????????????????????????????????
??????? i=i-1;
??????? if(i<=0)
??????? {
??????????? System.out.println("這里彈個對話框 沒有上一條了！！！");
???????????JOptionPane.showMessageDialog(null,"沒有上一條了！！！","",JOptionPane.ERROR_MESSAGE);
??????????? i=i+1;//上面已經執行了i=i-1，這里要i=i+1，保持i最小不小于0
??????? }
??????? else
??????? {
??????????? Dao_check.Check_next(i,str);
???????????jTextField1_Username.setText(Dao_check.getUsername());
???????????jTextField4_Title.setText(Dao_check.getTitle());
???????????jTextField3_Memotime.setText(Dao_check.getMemotime());
???????????jTextField2_Memotype.setText(Dao_check.getMemotype());
???????????jTextArea1_Content.setText(Dao_check.getContent());
??????? }
下一條//
i=i+1;
??????? if(Dao_check.rownum()<i)
???????{
??????????? System.out.println("這里彈個對話框 沒有下一條了！！！");
???????????JOptionPane.showMessageDialog(null,"沒有下一條了！！！","",JOptionPane.ERROR_MESSAGE);
??????????? i=i-1;//上面已經執行了i=i+1，這里要i=i-1，保持i最大不超過行的個數
??????? }
??????? else
??????? {
??????????? Dao_check.Check_next(i,str);
???????????jTextField1_Username.setText(Dao_check.getUsername());
???????????jTextField4_Title.setText(Dao_check.getTitle());
???????????jTextField3_Memotime.setText(Dao_check.getMemotime());
??????????? jTextField2_Memotype.setText(Dao_check.getMemotype());
???????????jTextArea1_Content.setText(Dao_check.getContent());
??????? }
//跳轉
private voidjButton3_ReturnActionPerformed(java.awt.event.ActionEvent evt) {???????????????????????????????????????????????
??????? CheckJFrame frame=new CheckJFrame();
??????? frame.setVisible(true);
??????? this .setVisible(false);// TODO addyour handling code here:
???}??????????????
??????
5.????????????????注意事項
1.??????代碼編寫規范，
2.??????實體的命名規范
3.??????代碼的正確性
4.??????包與包之間的連接
5.??????構造方法的編寫
6.??????數據庫的連接
7.??????方法的調用
8.??????數據庫的關閉?????????????????????????????
??????????????????????????????????
6.????????????????參考文獻
1.??????老師給的文檔代碼
2.??????百度代碼文檔
3.??????課本
4.??????百度搜索視頻講解
?
總結
- 上一篇： ubuntu双系统引导梅花_Win10+
- 下一篇： 语法基础（三. 类，属性，方法，方法重载