Question

jsp中用servlet连接数据库

Answer 1

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.imageio.stream.FileImageInputStream;

public class DBUtil {
private Connection conn;
private Statement stmt;
private PreparedStatement pps;
private ResultSet rs=null;
private String url="jdbc:oracle:thin:@localhost:1521:orcl";

static{
try {//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private Connection getConnection(){
try {//获取连接
return DriverManager.getConnection(url,"username","password");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int update(String sql){
int row=-1;
try {
conn=getConnection();

stmt=conn.createStatement();
row=stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return row;
}

public int update(String sql,Object ...obj){
//Object ...obj 动态数组 类型是Object，数组名为obj
int row=-1;
try {
conn=getConnection();
pps=conn.prepareStatement(sql);//预处理语句对象
for(int i=0;i<obj.length;i++){
if(obj[i] instanceof File){//将文件写入数据库
FileInputStream fis=new FileInputStream((File)obj[i]);
pps.setBinaryStream(i+1, fis, (int)((File)obj[i]).length());
continue;
}
pps.setObject(i+1, obj[i]);
}
row=pps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return row;
}

public List<Map<String,Object>> queryToList(String sql){
try {
conn=getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
ResultSetMetaData rsmd=rs.getMetaData();//获取列的信息
List<Map<String,Object>> list=new LinkedList<Map<String,Object>>();
while(rs.next()){
Map<String,Object> map=new HashMap<String,Object>();
for(int i=1;i<=rsmd.getColumnCount();i++){
map.put(rsmd.getColumnName(i), rs.getObject(i));//把每列信息封装成map
}
list.add(map);//map列信息封装进list
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}

public List<Map<String,Object>> queryToList(String sql,Object ...obj){
try {
conn=getConnection();
//stmt=conn.createStatement();
pps=conn.prepareStatement(sql);
for(int i=0;i<obj.length;i++){
pps.setObject(i+1, obj[i]);
}
rs=pps.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();//获取列的信息
List<Map<String,Object>> list=new LinkedList<Map<String,Object>>();
while(rs.next()){
Map<String,Object> map=new HashMap<String,Object>();
for(int i=1;i<=rsmd.getColumnCount();i++){
map.put(rsmd.getColumnName(i), rs.getObject(i));//把每列信息封装成map
}
list.add(map);//map列信息封装进list
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return null;
}

public PageBean getPage(String sql,int pagesize,int pagenow){
PageBean pb=new PageBean();
pb.setPagesize(pagesize);
pb.setPagenow(pagenow);
pb.setRowstart((pagenow-1)*pagesize+1);
pb.setRowend(pb.getRowstart()+pagesize-1);
pb.setRowall(queryToList(sql).size());
pb.setPageall(pb.getRowall()%pagesize==0?pb.getRowall()/pagesize:pb.getRowall()/pagesize+1);
//sql="select * from (select rownum as rowindex,t.* from ("+sql+" and rownum<='"+pb.getRowend()+"') t) where rowindex>='"+pb.getRowstart()+"'";
sql="select * from (select t.*,rownum as rowindex from ("+sql+")t where rownum<="+pb.getRowend()+") where rowindex>="+pb.getRowstart();
pb.setList(queryToList(sql));
return pb;
}

private void close(){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(pps!=null){
try {
pps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
最上面的是连接数据库的，下面的是方法的封装，用的时候直接定义一个对象调用方法就好了。当然别忘了下载驱动包

Answer 2

import java.sql.*;

public class DBconn {
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;

// 创建数据库连接
private void getConnection() {
String DBURL = "com.mysql.jdbc.Driver";
String DBDRIVER = "jdbc:mysql://localhost:3306/数据库名字";
String DBROOT = "root";
String DBPWD = "root";
try {
Class.forName(DBURL);
conn = DriverManager.getConnection(DBDRIVER, DBROOT, DBPWD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 定义增删改
public int exeADU(String sql) {
getConnection();
try {
stmt = conn.createStatement(); // 得到statement对象
int iRS = stmt.executeUpdate(sql);
return iRS;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1;
}

}

// 定义查询方法
public ResultSet exeQuery(String sql) {
getConnection();

try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
return rs;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}

}

// 关闭数据库连接
public void exeClose() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

Answer 3

Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://"+host+"/"+dbname,username,password);
Statement Stmt=conn.createStatement();
ResultSet rs=Stmt.executeQuery(sql);
这是基本的数据库操作方法
大概流程就是加载驱动类，创建连接，执行数据库操作，关闭

Answer 4

没他嘛的区别。写上jdbc代码就行

Answer 5

你到底是想在jsp还是在servlet里面连接呢？