求JSP向mysql插入，删除，查询数据源代码

还有这样的需求呀？无奇不有……

写的dao的实现就行了呀 ，何必得要jsp呢？用jsp，只会越来越乱^

CustomerDao.java

package cn.itcast.dao

import java.util.List

import cn.itcast.domain.Customer

public interface CustomerDao {

public void add(Customer customer)

public Customer find(int id)

public List getAllCustomer()

public void delete(int id)

public void update(Customer customer)

public int getAllRecord()

public List getCustomerByPage(int startindex,int pagesize)

}

CustomerDaoJdbcImpl.java

package cn.itcast.dao.impl

import java.sql.Connection

import java.sql.PreparedStatement

import java.sql.ResultSet

import java.util.ArrayList

import java.util.List

import cn.itcast.dao.CustomerDao

import cn.itcast.domain.Customer

import cn.itcast.util.JdbcUtils

public class CustomerDaoJdbcImpl implements CustomerDao {

/*

id int primary key auto_increment,

name varchar(20) not null,

sex varchar(4) not null,

birthday date,

cellphone varchar(20) not null,

Email varchar(40),

preference varchar(100),

type varchar(40),

Description varchar(255)

*/

public void add(Customer customer) {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "insert into customer(name,sex,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?)"

st = conn.prepareStatement(sql)

st.setString(1, customer.getName())

st.setString(2, customer.getSex())

st.setDate(3, new java.sql.Date(customer.getBirthday().getTime()))

st.setString(4, customer.getCellphone())

st.setString(5, customer.getEmail())

st.setString(6, customer.getPreference())

st.setString(7, customer.getType())

st.setString(8, customer.getDescription())

st.executeUpdate()

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

public void delete(int id) {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "delete from customer where id=?"

st = conn.prepareStatement(sql)

st.setInt(1, id)

st.executeUpdate()

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

public Customer find(int id) {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "select id,name,sex,birthday,cellphone,email,preference,type,description from customer where id=?"

st = conn.prepareStatement(sql)

st.setInt(1, id)

rs = st.executeQuery()

if(rs.next()){

Customer c = new Customer()

c.setId(rs.getInt("id"))

c.setName(rs.getString("name"))

c.setSex(rs.getString("sex"))

c.setBirthday(rs.getDate("birthday"))

c.setCellphone(rs.getString("cellphone"))

c.setEmail(rs.getString("email"))

c.setPreference(rs.getString("preference"))

c.setType(rs.getString("type"))

c.setDescription(rs.getString("description"))

return c

}

return null

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

/*

Id 编号 varchar(20)

name 客户姓名 varchar(20)

sex 性名 varchar(4)

birthday 生日 date

cellphone 手机 varchar(20)

Email 电子邮件 varchar(40)

preference 客户爱好 varchar(100)

type 客户类型 varchar(40)

Description 备注 varchar(255)

*/

public List getAllCustomer() {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "select id,name,sex,birthday,cellphone,email,preference,type,description from customer order by id"

st = conn.prepareStatement(sql)

rs = st.executeQuery()

List list = new ArrayList()

while(rs.next()){

Customer c = new Customer()

c.setId(rs.getInt("id"))

c.setName(rs.getString("name"))

c.setSex(rs.getString("sex"))

c.setBirthday(rs.getDate("birthday"))

c.setCellphone(rs.getString("cellphone"))

c.setEmail(rs.getString("email"))

c.setPreference(rs.getString("preference"))

c.setType(rs.getString("type"))

c.setDescription(rs.getString("description"))

list.add(c)

}

return list

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

public void update(Customer customer) {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "update customer set name=?,sex=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"

st = conn.prepareStatement(sql)

st.setString(1, customer.getName())

st.setString(2, customer.getSex())

st.setDate(3, new java.sql.Date(customer.getBirthday().getTime()))

st.setString(4, customer.getCellphone())

st.setString(5, customer.getEmail())

st.setString(6, customer.getPreference())

st.setString(7, customer.getType())

st.setString(8, customer.getDescription())

st.setInt(9, customer.getId())

st.executeUpdate()

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

public int getAllRecord() {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "select count(*) from customer"

st = conn.prepareStatement(sql)

rs = st.executeQuery()

if(rs.next()){

return rs.getInt(1)

}

return 0

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

public List getCustomerByPage(int startindex, int pagesize) {

Connection conn = null

PreparedStatement st = null

ResultSet rs = null

try{

conn = JdbcUtils.getConnection()

String sql = "select id,name,sex,birthday,cellphone,email,preference,type,description from customer limit ?,?"

st = conn.prepareStatement(sql)

st.setInt(1, startindex)

st.setInt(2, pagesize)

rs = st.executeQuery()

List list = new ArrayList()

while(rs.next()){

Customer c = new Customer()

c.setId(rs.getInt("id"))

c.setName(rs.getString("name"))

c.setSex(rs.getString("sex"))

c.setBirthday(rs.getDate("birthday"))

c.setCellphone(rs.getString("cellphone"))

c.setEmail(rs.getString("email"))

c.setPreference(rs.getString("preference"))

c.setType(rs.getString("type"))

c.setDescription(rs.getString("description"))

list.add(c)

}

return list

}catch(Exception e){

throw new RuntimeException(e)

}finally{

JdbcUtils.release(rs, st, conn)

}

}

}

import java.sql.*

public class DbOperator {

public final String DB_USER= "root"

public final String DB_PWD = "1234"链搜历

public final String DB_HOST = "127.0.0.1"

public final String DB_NAME = "test"

public DbOperator() {

}

/**

* 得到数据库连接

* @return Connection

*/

public Connection getConnection()

{

Connection conn = null

String url = "jdbc:mysql://"+this.DB_HOST+"/"+this.DB_NAME+"?useUnicode=true&characterEncoding=GBK"

try

{

Class.forName("com.mysql.jdbc.Driver").newInstance()

conn = java.sql.DriverManager.getConnection(url, this.DB_USER, this.DB_PWD)

}catch(Exception e)

{

e.printStackTrace()

}

return conn

}

}

使用的

Connection conn = dbOperator.getConnection()

try

{

Statement st = conn.createStatement()

String sql = " select * from user where username ='" + username + "' and pwd ='" + pwd + "棚搜'漏者"

ResultSet rs = st.executeQuery(sql)

if(rs.next())

{

userInfo = new UserInfo()

userInfo.setAge(rs.getString("age"))

userInfo.setId(rs.getInt("id"))

userInfo.setPwd(rs.getString("pwd"))

userInfo.setSex(rs.getString("sex"))

userInfo.setTheName(rs.getString("the_name"))

userInfo.setUserName(rs.getString("username"))

}

rs.close()

st.close()

conn.close()

}catch(Exception e)

{

e.printStackTrace()

}

return userInfo

首先工程lib下要有mysql的连接包，如果不是工程，就要在系统环境变量困毕的path里加上“d:/jar/mysql-connector-java-5.1.7-bin.jar” ，当然路径是根据你自己所在包位置写。

<%@ page contentType="text/htmlcharset=utf-8" import="java.sql.*" %>

<%

String dbClassName = "org.gjt.mm.mysql.Driver"//定义保存数据库驱动的变量

String dbUrl ="汪衫芹jdbc:mysql://localhost:3306/testshop"//testshop为你的数据库名

String dbUser = "root" //数据库管理员名

String dbPwd = "admin"//数据库管理员密码塌衫,根据你自己的修改

Statement stmt =null

Connection conn = null

try {

Class.forName(dbClassName).newInstance()

conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd)

} catch (Exception ee) {

ee.printStackTrace()

}

conn = getConnection()// 调用getConnection()方法构造Connection对象的一个实例conn

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY) //写不下，和上面句是连着的

ResultSet rs = stmt.executeQuery("select * from employee")//一个查询sql举例,返回ResultSet

int status = stmt.executeUpdate("insert into employee values ('name','password')") //一个更新sql举例，返回int类型，代表成功与否

%>

全部手打，请采纳，谢谢

---