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----无参数查询
<%@ 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>引用sqlhelper</title>
</head>
<body>
<body>
<table border="1">
<%!Connection conn = null;
Statement st = null;
ResultSet rs = null;%>
<%
try {
//建立数据库连接
conn = Sqlhelper.getConnection();
String sql = "select top 10 * from mobile";
rs = Sqlhelper.doQuery1(sql);
while (rs.next()) {
%>
<tr>
<td><%=rs.getInt("mobileID")%></td>
<td><%=rs.getString("mobileName")%></td>
<td><%=rs.getFloat("mobilePrice")%></td>
<td><%=rs.getString("mobilePicture")%></td>
</tr>
<%
}
} catch (SQLException ex) {
out.println(ex.getMessage());
} finally {
Sqlhelper.closeAll(rs, st, conn);
}
%>
</table>
</body>
</html>
运行效果
引用示例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>
运行效果