modelonedao.java

ajax+struts最简单代码

/*
 * Created on 2005-8-1
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package org.mstar.strutsajax;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.mstar.strutsajax.form.*;

/**
 * @author matianyi
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class ModelOneDAO {
	private Connection conn;

	public ModelOneDAO() {
		String url = "jdbc:jtds:sqlserver://172.17.2.52:1433/AjaxStruts";
		String user = "ajax";
		String password = "ajax";
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, user, password);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List getAllRows() throws SQLException {
		List result = new ArrayList();
		String sql = "select * from AJAX_MODEL_ONE";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			TableRowBean trb = new TableRowBean();
			trb.setCol1Value("" + rs.getInt("col1"));
			trb.setCol2Value("" + rs.getInt("col2"));
			trb.setCol3Value("" + rs.getInt("col3"));
			result.add(trb);
		}
		return result;
	}

	public List getSortedRows(int columnNo, boolean order) throws SQLException {
		List result = new ArrayList();
		StringBuffer sb = new StringBuffer("select * from AJAX_MODEL_ONE");
		switch (columnNo) {
		case TableModelBean.COLUMN_1:
			sb.append(" order by col1 ").append(order ? "asc" : "desc");
			break;
		case TableModelBean.COLUMN_2:
			sb.append(" order by col2 ").append(order ? "asc" : "desc");
			break;
		case TableModelBean.COLUMN_3:
			sb.append(" order by col3 ").append(order ? "asc" : "desc");
			break;
		default:
			sb.append(" order by col3 ").append(order ? "asc" : "desc");
		}
		String sql = sb.toString();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			TableRowBean trb = new TableRowBean();
			trb.setCol1Value("" + rs.getInt("col1"));
			trb.setCol2Value("" + rs.getInt("col2"));
			trb.setCol3Value("" + rs.getInt("col3"));
			result.add(trb);
		}
		return result;
	}

	/**
	 * @param key
	 * @throws SQLException
	 */
	public void deleteRow(int key) throws SQLException {
		String sql = "delete from AJAX_MODEL_ONE where col1 = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, key);
		ps.executeUpdate();
	}

	public void updateRow(TableRowBean row) throws SQLException {
		String sql = "update AJAX_MODEL_ONE set col2 = ?,col3 = ? where col1 = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(row.getCol2Value()));
		ps.setInt(2, Integer.parseInt(row.getCol3Value()));
		ps.setInt(3, Integer.parseInt(row.getCol1Value()));
		ps.executeUpdate();
	}

	public void addRow(TableRowBean row) throws SQLException {
		String sql = "insert into AJAX_MODEL_ONE (col1,col2,col3) values (?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(row.getCol1Value()));
		ps.setInt(2, Integer.parseInt(row.getCol2Value()));
		ps.setInt(3, Integer.parseInt(row.getCol3Value()));
		ps.executeUpdate();
	}

	/**
	 * @throws SQLException
	 *  
	 */
	public int getNextId() throws SQLException {
		String sql = "select max(col1)+1 as nextid from ajax_model_one";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getInt("nextid");
		} else {
			throw new SQLException("no record!");
		}
	}

	public static void main(String[] args) {
		ModelOneDAO dbp = new ModelOneDAO();
		try {
			List resultList = dbp.getSortedRows(1, true);
			System.out.println(resultList.size());
			Iterator iter = resultList.iterator();
			while (iter.hasNext()) {
				TableRowBean trb = (TableRowBean) iter.next();
				System.out.println(trb.getCol1Value() + "\t"
						+ trb.getCol2Value() + "\t" + trb.getCol3Value());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public TableRowBean getSingleRow(int key) throws SQLException {
		String sql = "select col1,col2,col3 from ajax_model_one where col1 = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, key);
		ResultSet rs = ps.executeQuery();
		TableRowBean row = new TableRowBean();
		while(rs.next()){
			row.setCol1Value(rs.getString("col1"));
			row.setCol2Value(rs.getString("col2"));
			row.setCol3Value(rs.getString("col3"));
		}
		return row;
	}

}