jsp struts 获取数据库数据以及增删改查等基本操作

最近开始学习java，花了不少时间做了一个练习。

新建一个webproject，再在src下配置一个struts-xml文件，同时配置web-inf下的web-xml，然后再连接mysql数据库。

mysql数据库：在mysql数据库中先建立一个userdb数据库，然后新建一张User表

 

create table user(  

    -> id int ( 4 ) primary key not  null  auto_increment,  

    -> username varchar(16 ) not  null ,  

    -> userpassword varchar(32 ) not  null）

练习按照mvc设计模式来编码：

首先model user ，定义一个class类User 

package orgmodel;

public class User {
private String userName;
private String passWord;
private String id;


public String getId() {
	return id;
}
public void setId(String id) {
	this.id = id;
}
public String getUserName() {
	return userName;
}
public void setUserName(String userName) {
	this.userName = userName;
}
public String getPassWord() {
	return passWord;
}
public void setPassWord(String passWord) {
	this.passWord = passWord;
}

public User(){
    
	this.userName=null;
	this.passWord=null;
}

public User(String id,String userName,String passWord){
	super();
	this.setId(id);
	this.userName=userName;
	this.passWord=passWord;
}

public String toString()
{
    return userName+""+passWord;
}

 其次是数据库的一些操作 mysql action：

package business;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class mysql {
    public Connection conn() throws Exception{
    	
     String DriveName="org.gjt.mm.mysql.Driver";
   	  String url="JDBC:mysql://localhost:3306/userdb";//数据库的端口，名字
   	  String user="root";
   	  String password="19891002ab";
   	Connection con=null;
	Statement stt=null;
	try {
			Class.forName(DriveName);		
			con = DriverManager.getConnection(url, user, password);
			stt=con.createStatement();
		} catch (SQLException ex) {
			System.err.println("SQL" + ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	
        return con;
    }
    
    public  ResultSet selectid(String id) throws Exception{
    	Connection con=null;
    	Statement stt=null;
    	ResultSet rs;
    	con=conn();
    	stt=con.createStatement();
    	String sql="select username, password form user where id="+id;
    	rs=stt.executeQuery(sql);
    	while(rs!=null){
    		return rs;
    	}
    	return null;
    }
    
    public ResultSet select() throws Exception{
    	Connection con=null;
    	Statement stt=null;
    	ResultSet rs;
    	con=conn();
    	stt=con.createStatement();
    	rs=stt.executeQuery("select * from user ");
    	while(rs!=null){
    		return rs;
    	}
		return null;
		
    }
    public ResultSet insert(String username,String password) throws Exception{
    	Connection con=null;
    	Statement stt=null;
    	ResultSet rs;
    	con=conn();
    	stt=con.createStatement();
    	String sql = "insert into user(username,password) values('"+username+"','"+password+"')";
    	stt.executeUpdate(sql);
    	rs=stt.getResultSet();
    	while(rs!=null){
    		return rs;
    	}
		return null;	
    }
    
    public ResultSet update(String username,String password,String id) throws Exception{
    	Connection con=null;
    	Statement stt=null;
    	ResultSet rs;
    	con=conn();
    	stt=con.createStatement();
    	stt.executeUpdate("update user set username='"+username+"' and password='"+password+"' where id='"+id+"'");
    	rs=stt.getResultSet();
    	while(rs!=null){
    		return rs;
    	}
		return null;	
    }
    
    public ResultSet delete(String id) throws Exception{
    	Connection con=null;
    	Statement stt=null;
    	ResultSet rs;
    	con=conn();
    	stt=con.createStatement();
    	String sql="delete from user where id='"+id+"'";
    	stt.executeUpdate(sql);
    	rs=stt.getResultSet();
    	while(rs!=null){
    		return rs;
    	}
		return null;	
    }
    
}

 最后是Action 查询：

package orgaction;



import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.interceptor.ServletRequestAware;
import orgmodel.User;

import business.mysql;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

public class LoginAction extends ActionSupport implements ServletRequestAware {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private List<User> userList= new ArrayList<User>();
	private List<User> usersLists= new ArrayList<User>();
	private HttpServletRequest  request;
	
	private  User userBean;

	public HttpServletRequest getRequest() {
		return request;
	}

	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}

	public User getUserBean() {
		return userBean;
	}

	public void setUserBean(User userBean) {
		this.userBean = userBean;
	}

	public void setServletRequest(HttpServletRequest arg0) {
		// TODO Auto-generated method stub
		this.setRequest(arg0);
		
	}

	public String excute() throws Exception{
		return Select();
		
	}

	public String Select() throws Exception {
		// TODO Auto-generated method stub
		@SuppressWarnings("unused")
		ResultSetMetaData rsmd;
		ResultSet rs;
		mysql mySql=new mysql();
		rs=mySql.select();
		if(rs!=null){
			rsmd=rs.getMetaData();
			while(rs.next()){
				User us=new User();
				us.setId(rs.getString(1));
				us.setUserName(rs.getString(2));
				us.setPassWord(rs.getString(3));
				userList.add(us);
				
			}
		}
		System.out.println(userList);
		
		ActionContext.getContext().getSession().put("list",userList);
		
		return "rsnd";
	
	}
	
	public String insert() throws Exception{
		 
		mysql mySql=new mysql();
		 
		if(userBean.getUserName()!=null && userBean.getPassWord()!=null){
		mySql.insert(userBean.getUserName(),userBean.getPassWord());
		 
		}
		System.out.println("-------"+userBean+"--------");
		return Select();
	}
	
	public String update() throws Exception{
		mysql mySql=new mysql();	 
		System.out.println("----nnnnnnnnn------"+mySql+"----------------");
		mySql.update(userBean.getUserName(),userBean.getPassWord(),userBean.getId());
		
		System.out.println("----------"+userBean+"----------mmmmmmm------");
		return Select();
	}
	
	public String delete() throws Exception{
		mysql mySql=new mysql();
		if(userBean.getId()!=null){
		System.out.println("-------"+userBean.getId()+"--------");
		mySql.delete(userBean.getId());
		}
		return Select();
		
	}
	
	public String updateuser() throws Exception{
		
		String userId=userBean.getId();
		System.out.println("-----------updateyuser"+userId+"---------------");
		mysql mySql=new mysql();
		ResultSet rs;
		if(userId!=null){
			rs=mySql.selectid(userId); 
			while(rs!=null){
				User us=new User();
				us.setUserName(rs.getString(1));
				us.setPassWord(rs.getString(2));
				usersLists.add(us);
			}
		}
		ActionContext.getContext().getSession().put("list",usersLists);
		return "Ok";
		
	}

	public List<User> getUserList() {
		return userList;
	}

	public void setUserList(List<User> userList) {
		this.userList = userList;
	}

	public List<User> getUsersLists() {
		return usersLists;
	}

	public void setUsersLists(List<User> usersLists) {
		this.usersLists = usersLists;
	}
}