查询部分

来源：互联网发布：ubuntu apache2 编辑：程序博客网时间：2024/05/16 14:18

查询备忘录

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. 百度搜索视频讲解

0 0