为了方便测试应用服务器是否可以代码连接数据库,在已经引入数据库驱动的前提下,直接用Jsp来测试会比较方便。只要复制Jsp文件拷到项目根目录即可,这样的好处是不用重启服务器。(才不是什么挂马脚本呢 哼!)
注意:数据库驱动需先导入,此代码只支持Mysql数据库测试。若想测试SqlServer、Oracle等其他数据库,请换相应的驱动并且修改加载驱动名部分代码。
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*,java.util.*" errorPage="" %>
<%!
//获取连接
public Connection getConnection(String dbName,String user,String password) throws Exception{
//加载驱动,默认mysql驱动
Class.forName("com.mysql.jdbc.Driver").newInstance();
//建立连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/"
+ dbName + "?user=" + user + "&password=" + password);
return conn;
}
//获取所有表格
public List<String> getTableName( Connection conn, String user, String database ) throws Exception{
DatabaseMetaData dbmd = conn.getMetaData();
// 表名列表
List<String> tableNames = new ArrayList<String>();
ResultSet rest = dbmd.getTables(database, null, null, new String[] { "TABLE" });
while (rest.next()) {
String tableSchem = rest.getString("TABLE_SCHEM");
tableNames.add(rest.getString("TABLE_NAME"));
}
return tableNames;
}
//获取表格的列名
public List<String> getTableColumnName( Connection conn,String tableName) throws Exception{
Statement stmt = conn.createStatement();
String sql="select * from " + tableName;
ResultSet rs = stmt.executeQuery(sql);
List<String> tableColumnName = new ArrayList<String>();
ResultSetMetaData data = rs.getMetaData();
for (int i = 1; i <= data.getColumnCount(); i++) {
tableColumnName.add(data.getColumnName(i));
}
rs.close();
stmt.close();
return tableColumnName;
}
//获取表格的数据
public List<List<String>> getCounts(Connection conn,String tableName,List<String> tableColumn) throws Exception{
Statement stmt = conn.createStatement();
String sql="select * from " + tableName;
ResultSet rs = stmt.executeQuery(sql);
List<List<String>> counts = new ArrayList<List<String>>();
while(rs.next())
{ List<String> count = new ArrayList<String>();
for(int i = 0; i < tableColumn.size(); i++) {
count.add(rs.getString(tableColumn.get(i)));
}
counts.add(count);
}
rs.close();
stmt.close();
return counts;
}
%>
<%
String user; //用户名
String pass; //密码
String db; //数据库名
String table; //表名
db = request.getParameter("db") == null ? null : request.getParameter("db");
user = request.getParameter("user") == null ? "root" : request.getParameter("user");
pass = request.getParameter("pass") == null ? "root" : request.getParameter("pass");
table = request.getParameter("table") == null ? null : request.getParameter("table");
Connection conn = null;
try{
conn = getConnection(db,user,pass);
}catch(Exception e){
out.print("<font color=red>"+e+"</font><br>");
}
if( conn == null ){
out.print("sorry,db is not open!");
}else{
//获取所有表名
List<String> tables = getTableName(conn,user,db);
for(int i=0;i<tables.size();i++){
out.println(tables.get(i)+"|");
}
if( table != null ){
//获取表的数据
List<String> columnName = getTableColumnName(conn,table);
List<List<String>> counts = new ArrayList<List<String>>();
counts = getCounts(conn,table,columnName);
out.print("<hr><br>");
for(int i=0;i<counts.size();i++){
for(int j=0;j<counts.get(i).size();j++){
out.print(counts.get(i).get(j)+"|");
}
out.print("<br>");
}
}
conn.close();
}
%>
接口参数详情:
参数名 | 是否必须 | 说明 |
db | 是 | 数据库名称 |
user | 是 | 登陆数据库用户名 |
pass | 是 | 登陆数据库密码 |
table | 否 | 表名 |
在浏览器地址栏输入相应的参数,当不输入表格名时会输出所有的表,如图:
输入表格名时,输出表的数据: