将jdbc常用操作封装为Sqlhelper类

小鱼儿 2024-02-18 20:00 7阅读 0赞

Sqlhelper类

package com.sqlHelper;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class Sqlhelper {
    	public  static Connection conn=null;
    	
    	public static Statement sta=null;
    	public static PreparedStatement psta=null;
    	
    	public static ResultSet rs=null;
    	
    	public static String mysqlDriver="com.mysql.jdbc.Driver";	
    	public static String mysqlDBurl="jdbc:mysql://localhost:1433/test?useSSL=false";
    	
    	public static String sqlserverDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    	public static String sqlserverDBurl="jdbc:sqlserver://localhost:1433;DatabaseName=test";
    	
    	public static String uid="sa";
    	public static String pwd="123456";
    	
    	// 注册驱动
    		static {
    			try {
    				Class.forName(sqlserverDriver);
    			} catch (ClassNotFoundException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    				System.out.println("注册驱动失败");
    			}
    		}
    
    		// 建立连接
    		public static Connection getConnection() {
    
    			try {
    				conn = DriverManager.getConnection(sqlserverDBurl, uid, pwd);
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    				System.out.println("建立连接失败");
    			}
    			return conn;
    		}
    	
    	
    	//select无参数查询
    	public static ResultSet doQuery1(String strsql) {
    		try {
    			sta=conn.createStatement();
    			rs = sta.executeQuery(strsql);
    		} catch (SQLException e) {			
    			e.printStackTrace();
    		}
    		return rs;		
    	}
    	
    	//select有参数查询，常用于验证，登录
    	//例如select ename,birthday,sal from emp where eno = ? and ename =?
    	//arg1,arg2为select语句中的参数
    	public static ResultSet doQuery2(String strsql,String arg1,String arg2) {
    		try {
    			//预编译语句对象
    			psta =  conn.prepareStatement(strsql);			
    			psta.setString(1, arg1);
    			psta.setString(2, arg2);
    			
    			rs = psta.executeQuery();
    		} catch (SQLException e) 
    		{			
    			e.printStackTrace();
    		}
    		return rs;		
    	}
    	
    	//插入，删除，修改
    		public static int doUpdate(String sql) {
    			int num=0;
    			Statement st=null;
    			try {
    				st = conn.createStatement();
    				num=st.executeUpdate(sql);
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    				num=-1;
    			}
    			return num;
    		}
    		
    	
    	
    	//关闭sql连接
    	public static void closeAll(ResultSet rs, Statement st, Connection conn) {
    		try {
    			if (rs != null)
    				rs.close();
    		} catch (SQLException e) {
    		} finally {
    			try {
    				if (st != null)
    					st.close();
    			} catch (SQLException e) {
    			} finally {
    				if (conn != null)
    					try {
    						conn.close();
    					} catch (SQLException e) {
    						// TODO Auto-generated catch block
    						e.printStackTrace();
    					}
    			}
    		}
    	}
    	
    	
    }

引用示例1----无参数查询

运行效果

![2018042919142559][]

引用示例2----添加数据

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
    <%@page import="com.sqlHelper.*,java.util.*,java.sql.*"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
    <%!Connection conn = null;
    	Statement st = null;
    	ResultSet rs = null;%>
    </head>
    <body>
    <form>
    		<table>
    			<tr>
    				<td>dno:</td>
    				<td><input type="text" name="dno" />
    				</td>
    			</tr>
    			
    			<tr>
    				<td>dname:</td>
    				<td><input type="text" name="dname" />
    				</td>
    			</tr>
    			<tr>
    				<td>phone:</td>
    				<td><input type="text" name="phone" />
    				</td>
    			</tr>
    			<tr>
    				<td colspan="2" align="center"><input type="submit"
    					name="submit" value="添加" />
    				</td>
    			</tr>
    		</table>
    	</form>
    	<%
    		String dno = request.getParameter("dno");
    		String dname = request.getParameter("dname");
    		
    		String phone = request.getParameter("phone");
    		if(dno==null||dname==null||phone==null)
    		{
    			out.print("<script>alert('未填写')</script>");
    		}else{
    			
    		
    
    			conn = Sqlhelper.getConnection();
    			//通过拼接字符串插入数据
    			String sql = "insert into test values('" + dno + "','" + dname + "','" + phone + "')";
    			int num=Sqlhelper.doUpdate(sql);
    			if(num>0){
    				out.print("<script>alert('添加成功')</script>");
    			}
    			Sqlhelper.closeAll(null, st, conn);
    		}
    	%>
    
    
    </body>
    </html>

运行效果

![20180429191906896][]

[2018042919142559]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b7e04d4ddac948b68d76de0c1bfcc157.png
[20180429191906896]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/eb1cbaa45ca84c7aa823a034737da3ee.png