更加精简的增删改查以及DBUtil方法

                                               方便精简                                           

上一个servlet的增删改查的代码量太大，这次为大家带来更加方便精简的方式。

( • ̀ω•́ )✧φ(>ω<*) 

这里用到了onject语句，所以使查询变得更加简单。

1.工具类

前三个方法不用动，只用写一个调用servlet语句的方法就行了。使用servlet语句的时候直接调用，更加方便。

package org.jun.Util;

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

public class DBUtil {



	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getconn() {

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databasename=MyDB", "sa",
					"1");
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
		try {
			if(conn!=null)
			conn.close();
			if(ps!=null)
				ps.close();
			if(rs!=null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public static int update(String sql, Object[] objs) {
		Connection conn = DBUtil.getconn();
		PreparedStatement ps = null;
		int count = 0;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				ps.setObject(i + 1, objs[i]);
			}
			 count = ps.executeUpdate();
			if (count > 0) {
				System.out.println("成功");
			} else {
				System.out.println("失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}
		return count;

	}
}

2.查询语句如下，稍微有点复杂。

package org.jun.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.jun.Util.DBUtil;
import org.jun.bean.User;

public class select {

	public static ArrayList<User> getUsers() {
		Connection conn = DBUtil.getconn();
		String sql = "select *from user";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<User> users = new ArrayList<User>();

		try {
			rs = ps.executeQuery();
			ps = conn.prepareStatement(sql);
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("1"));
				user.setUserName(rs.getString("2"));
				user.setPwd(rs.getString("3"));
				user.setSex(rs.getString("4"));
				user.setAge(rs.getInt("5"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return users;
	}
}

3.这是剩下三个语句，用了新的方法，这三个变得特别简单了。

package org.jun.dao;
import org.jun.Util.DBUtil;

public class update {
	
	public static void main(String[] args) {
		
		//这是更新------------------------
		String sql = "update student set pwd = ?and id = ?";
		Object[] objs = { "223", 2 };
		int count = DBUtil.update(sql, objs);
		if (count > 0) {
			System.out.println("成功");
		} else {
			System.out.println("失败");
		}
        
		//这是新增------------------------
		String sql2 = "insert into user (user_name,pwd,sex,age)";
		Object[] objs2 = { "zhangsan", "213", "nan", 18 };
		int count2 = DBUtil.update(sql2, objs2);
		if (count2 > 0) {
			System.out.println("成功");
		} else {
			System.out.println("失败");
		}
		
		//这是删除------------------------
		String sql3 = "(delete from user where id = ?)";
		Object[] objs3 = {2};
		int count3 = DBUtil.update(sql3, objs3);
		if (count3 > 0) {
			System.out.println("成功");
		} else {
			System.out.println("失败");
		}
	}
}