Notes弹窗查询异构系统数据库解决方案

一、文章背景

关于Lotus Notes查询功能用过的就知道有多烂，用过的都知道。

二、项目效果

点击按钮→弹出窗口→输入条件→查询→选择结果→确定→数据会写界面

三、使用技术

LS2J、Java AWT、JDBC

四、具体实现步骤

4.1、表单字段

字段：MergeSupplierCode
字段：MergeSupplierName

4.2、表单按钮的LotusScript代码

Sub Click(Source As Button)
	Dim cBasic As New BasicTool	
	'获取数据库连接信息
	Dim Session As New NotesSession
	' --基础数据配置库
	Dim BasicDB As NotesDatabase
	' --数据库信息视图
	Dim DBInfoView As NotesView
	' --数据库配置记录
	Dim DBInfo As Notesdocument
	' --本项目把数据库的连接信息用另外一个Notes数据库里记录着，需要用的时候通过代码实时查，若只是测试，可以直接写死在代码里
	Set BasicDB = Session.GetDatabase("oa/landworld","Landworld\BaseConfig.nsf")
	Set DBInfoView = BasicDB.GetView("vwSAPDB")
	' --要查询的数据库的账号密码信息，
	' -- DBInfo.IPAdr(0) 数据库IP地址
	' -- DBInfo.username(0) 数据库用户名
	' -- DBInfo.password(0) 数据库密码
	' -- DBInfo.DBName(0) 数据库实例名
	Set DBInfo = DBInfoView.GetdocumentByKey("DEV",True)
	
	Dim Uidoc As NotesUIDocument
	Dim Ws As New NotesUIWorkspace
	Dim Curdoc As Notesdocument
	Set Db = Session.CurrentDatabase
	Set Uidoc = Ws.CurrentDocument
	Set Curdoc= Uidoc.Document
	Dim jsession As JavaSession
	Dim jclass As JavaClass
	Dim myObject As JavaObject
	Dim vender As JavaObject
	Set jsession = New JavaSession
	Set jclass = jsession.GetClass("Untitled")
	Set myObject = jclass.CreateObject()
	' 弹出查询窗口
	Set vender = myObject.getVender(DBInfo.IPAdr(0),DBInfo.username(0),DBInfo.password(0),DBInfo.DBName(0))
	If Not Trim(vender.getVenderID()) = "" Then
		CurDoc.MergeSupplierCode = vender.getVenderID()
	End If
	If Not Trim(vender.getVenderName()) = "" Then
		CurDoc.MergeSupplierName = vender.getVenderName()
	End If
End Sub

4.3、后台JAVA脚本库

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库工具类
 * @author IT
 *
 */
public class DBUtil {

	/**
	 * 
	 * @param DBIPAdr 数据库IP地址
	 * @param DBUSN 数据库用户名
	 * @param DBPWD 数据库密码
	 * @param DBName 数据库实例名
	 * @return
	 */
	public static Connection getDBconn(String DBIPAdr,String DBUSN,String DBPWD,String DBName){
		Connection con = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection("jdbc:sqlserver://" + DBIPAdr + ":1433;DatabaseName=" + DBName + "",DBUSN, DBPWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	/**
	 * 数据库关闭
	 * @param con
	 * @param stmt
	 * @param rst
	 */
	public static void close(Connection con,Statement stmt,ResultSet rst){
		try {
			if (null != rst) {
				rst.close();
			}
			if (null != stmt) {
				stmt.close();
			}
			if (null != con) {
				con.close();
			}
		} catch (Exception e) {
			System.out.println("数据库关闭异常："+e.toString());
		}
	}
	
	/**
	 * 数据库关闭
	 * @param con
	 * @param stmt
	 */
	public static void close(Connection con,Statement stmt){
		try {
			if (null != stmt) {
				stmt.close();
			}
			if (null != con) {
				con.close();
			}
		} catch (Exception e) {
			System.out.println("数据库关闭异常："+e.toString());
		}
	}
}

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

/**
 * 
 * @author IT
 *
 */
public class VenderServer {

	/**
	 * 查询供应商信息
	 * @param CardCode
	 * @param CardName
	 * @return
	 * @throws SQLException
	 */
	public List<Vender> getVenders(String CardCode,String CardName,String DBIPAdr,String DBUSN,String DBPWD,String DBName) {
		List<Vender> res = new ArrayList<Vender>();
		
		try {
			String sql = "select CardCode ,CardName,CardType from OCRD WHERE CardType = 'S' ";
			if (StringUtil.isValid(CardCode)) {
				sql = sql + "AND CardCode LIKE '%" + CardCode + "%' ";
			}
			if (StringUtil.isValid(CardName)) {
				sql = sql + "AND CardName LIKE '%" + CardName + "%' ";
			}
			Connection con = DBUtil.getDBconn(DBIPAdr,DBUSN,DBPWD,DBName);
			Statement stmt = con.createStatement();
			ResultSet rst = stmt.executeQuery(sql);
			while (rst.next()) {
				res.add(new Vender(rst.getString("CardCode"), rst
						.getString("CardName")));
			}
			DBUtil.close(con, stmt, rst);
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,"VenderServer getVenders()"+e.toString(), "错误提示",JOptionPane.ERROR_MESSAGE);
		}
		return res;
	}
	
}

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.SwingUtilities;
import javax.swing.table.DefaultTableModel;

public class Untitled {
	// 窗口宽高
	private static int windowWidth = 800;
	private static int windowHeigh = 500;
	// 屏幕宽高 默认值
	private int screenWidth = 1024;
	private int screenHeigh = 768;
	// 结果表的表头
	private static String[] resultTableHeader = new String[] {"序号","编码","描述"};
	// 结果表格
	private JTable resultTable = null;
	private DefaultTableModel resultTableModel = null;
	// 编码输入，描述输入
	private JTextField codeField,nameField = null;
	// 返回结果（这样写页面上不报错）
	Vender vender = new Vender("","");
	

	/**
	 * 供应商信息查询弹窗
	 * @param DBIPAdr 数据库IP
	 * @param DBUSN	数据库账号
	 * @param DBPWD 数据库密码
	 * @param DBName 数据库实例名
	 * @return
	 */
	public Vender getVender(final String DBIPAdr,final String DBUSN,final String DBPWD,final String DBName) {
		
        JPanel content = new JPanel();
        content.setLayout(new BorderLayout());
        // 北
		JPanel northP = new JPanel(new FlowLayout());
		JPanel northPLeft = new JPanel(new GridLayout(2,1));
		JPanel northPCenter = new JPanel(new GridLayout(2,1));
		JPanel northPRigtt = new JPanel(new FlowLayout());
		
		JLabel codeLabel = new JLabel("供应商代码：  ");
		northPLeft.add(codeLabel);
		JLabel nameLabel = new JLabel("供应商描述：  ");
		northPLeft.add(nameLabel);
		northP.add(northPLeft);
		
		codeField = new JTextField(55);
		northPCenter.add(codeField);
		nameField = new JTextField(55);
		northPCenter.add(nameField);
		northP.add(northPCenter);
		
		JButton querBtn = new JButton("查  询");
		querBtn.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent arg0) {
				String code = codeField.getText();
				String name = nameField.getText();
				if (!StringUtil.isValid(code) && !StringUtil.isValid(name)) {
					javax.swing.JOptionPane.showMessageDialog(null,"供应商代码和供应商描述不能都为空！", "错误提示",JOptionPane.ERROR_MESSAGE);
					return;
				}
				VenderServer server = new VenderServer();
				List<Vender> venders = server.getVenders(code, name,DBIPAdr,DBUSN,DBPWD,DBName);
				// 清空上一次的记录
				int rowCount = resultTable.getRowCount();
				for (int i = 0; i < rowCount; i++) {
					resultTableModel.removeRow(rowCount - 1 - i);
				}
				// 结果展示
				int i = 0;
				for (Vender vender : venders) {
					String[] row = {++i + "",vender.getVenderID(),vender.getVenderName()};
					resultTableModel.addRow(row);
				}
				if (i<=0){
					javax.swing.JOptionPane.showMessageDialog(null,"按输入条件查询为空！", "消息提示",JOptionPane.INFORMATION_MESSAGE);
					return;
				}
			}
		});
		northPRigtt.add(querBtn);
		northP.add(northPRigtt);
		content.add(northP,BorderLayout.NORTH);
		
		// 中  明细表
		String date[][] = {};
		resultTableModel = new DefaultTableModel(date, resultTableHeader);
		resultTable = new JTable(resultTableModel){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int row, int column) { 
			 	 return false;
			  }
		};
		resultTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); // 单选 
		resultTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); // 关闭列宽自动调整
		resultTable.getColumnModel().getColumn(0).setPreferredWidth(60);
		resultTable.getColumnModel().getColumn(1).setPreferredWidth(100);
		resultTable.getColumnModel().getColumn(2).setPreferredWidth(610);
		JScrollPane eventScroll = new JScrollPane(resultTable);
		eventScroll.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
		eventScroll.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
		content.add(eventScroll,BorderLayout.CENTER);
		
		// 南
		JPanel southP = new JPanel(new FlowLayout(FlowLayout.RIGHT));
		JButton sureBtn = new JButton("确  定");
		sureBtn.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent arg0) {
				int selectedRow = resultTable.getSelectedRow();
				if (-1 == selectedRow){
					javax.swing.JOptionPane.showMessageDialog(null,"请先选择需要的数据", "警告提示",JOptionPane.WARNING_MESSAGE);
					return;
				}
				String venderCode = resultTableModel.getValueAt(selectedRow, 1).toString();
				String venderName = resultTableModel.getValueAt(selectedRow, 2).toString();
				vender.setVenderID(venderCode);
				vender.setVenderName(venderName);
				
				JButton button = (JButton)arg0.getSource();
                SwingUtilities.getWindowAncestor(button).dispose();
			}
		});
		southP.add(sureBtn);
		southP.add(new JLabel("  |  "));
		JButton cancleBtn = new JButton("取  消");
		cancleBtn.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent arg0) {
				JButton button = (JButton)arg0.getSource();
                SwingUtilities.getWindowAncestor(button).dispose();
			}
		});
		southP.add(cancleBtn);
		content.add(southP,BorderLayout.SOUTH);
		
		
		JDialog dialog = new JDialog();
		dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		dialog.setModal(true);
		dialog.setTitle("供应商信息查询窗口");
		dialog.getContentPane().add(content);
		dialog.setSize(windowWidth, windowHeigh);
		dialog.setResizable(false);
		dialog.pack();
		Toolkit kit = Toolkit.getDefaultToolkit();
		Dimension screenSize = kit.getScreenSize();
		screenWidth = (int)screenSize.getWidth();
		screenHeigh = (int)screenSize.getHeight();
		dialog.setLocation(screenWidth/2 - windowWidth/2, screenHeigh/2 - windowHeigh/2);
		dialog.setLocationRelativeTo(null);
		dialog.setVisible(true);
		
        
        return vender;
	
	}
	
}

/**
 * String 工具类
 * @author IT
 *
 */
public class StringUtil {
	/**
	 * 判断字符串是否有效
	 * @param str
	 * @return true:str非空,false:str为null或空白字符串
	 */
	public static boolean isValid(String str) {
		if (null == str || "".equals(str.trim())) {
			return false;
		}

		return true;
	}
}

/**
 * 供应商信息
 * 
 * @author IT
 * 
 */
public class Vender {
	private String venderID;
	private String venderName;

	public String getVenderID() {
		return venderID;
	}

	public void setVenderID(String venderID) {
		this.venderID = venderID;
	}

	public String getVenderName() {
		return venderName;
	}

	public void setVenderName(String venderName) {
		this.venderName = venderName;
	}

	public Vender(String venderID, String venderName) {
		super();
		this.venderID = venderID;
		this.venderName = venderName;
	}

	public Vender() {
		super();
	}
}

4.4、表单引入后台JAVA库

Uselsx “*javacon”
Use “QueryVender”

4.5、依赖包及代码

点击下载，该资源不要积分

五、坑

每次修改Java代码后，要重启Notes客户端，包括开发工具