自写JDBCUtil工具类&tomcat下配置实现JDBCUtil简化
JDBCUtil.java
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.PreparedStatement; 4 import java.sql.ResultSet; 5 import java.sql.SQLException; 6 7 public class JdbcUtil { 8 private static String CLASSNAME="com.mysql.jdbc.Driver"; 9 private static String URL="jdbc:mysql://localhost:3306/test"; 10 private static String USERNAME="root"; 11 private static String PASSWORD="root"; 12 private Connection conn=null; 13 private PreparedStatement pst=null; 14 private ResultSet rs=null; 15 static{ 16 try { 17 Class.forName(CLASSNAME); 18 } catch (ClassNotFoundException e) { 19 e.printStackTrace(); 20 } 21 } 22 23 public Connection getConnection(){ 24 try { 25 conn = DriverManager.getConnection(URL,USERNAME,PASSWORD); 26 } catch (SQLException e) { 27 e.printStackTrace(); 28 } 29 return conn; 30 } 31 32 public ResultSet query(String sql,Object... param){ 33 getConnection(); 34 try { 35 pst = conn.prepareStatement(sql); 36 if(param!=null){ 37 for(int i=0;i<param.length;i++){ 38 pst.setObject(i+1, param[i]); 39 } 40 } 41 rs = pst.executeQuery(); 42 return rs; 43 } catch (SQLException e) { 44 e.printStackTrace(); 45 } 46 return null; 47 } 48 public int update(String sql,Object... param){ 49 getConnection(); 50 try { 51 pst = conn.prepareStatement(sql); 52 if(param!=null){ 53 for(int i=0;i<param.length;i++){ 54 pst.setObject(i+1, param[i]); 55 } 56 } 57 int i = pst.executeUpdate(); 58 return i; 59 } catch (SQLException e) { 60 e.printStackTrace(); 61 } 62 return 0; 63 } 64 65 public void close(){ 66 try{ 67 if(conn!=null){ 68 conn.close(); 69 } 70 if(pst!=null){ 71 pst.close(); 72 } 73 if(rs!=null){ 74 rs.close(); 75 } 76 }catch(Exception e){ 77 e.printStackTrace(); 78 } 79 } 80 }
对loginServlet.java的简化：
1 import java.io.IOException; 2 import java.sql.Connection; 3 import java.sql.DriverManager; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import javax.servlet.ServletException; 11 import javax.servlet.annotation.WebServlet; 12 import javax.servlet.http.HttpServlet; 13 import javax.servlet.http.HttpServletRequest; 14 import javax.servlet.http.HttpServletResponse; 15 16 import com.lq.model.Book; 17 import com.lq.util.JdbcUtil; 18 19 /** 20 * Servlet implementation class LoginServlet 21 */ 22 @WebServlet("/LoginServlet") 23 public class LoginServlet extends HttpServlet { 24 JdbcUtil jdbc = new JdbcUtil(); 25 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 26 //设置请求的编码格式 27 request.setCharacterEncoding("UTF-8"); 28 String username = request.getParameter("username"); 29 String password = request.getParameter("password"); 30 boolean flag =query(username,password); 31 //f6 下一步 f8下一个断点(无断点,程序执行结束) 32 if(flag){ 33 List<Book> books = query_book(); 34 request.setAttribute("books", books); 35 request.getRequestDispatcher("index.jsp").forward(request, response); 36 }else{ 37 request.setAttribute("flag", "用户名或密码错误，请重新输入"); 38 request.getRequestDispatcher("login.jsp").forward(request, response); 39 } 40 } 41 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 42 doGet(request, response); 43 } 44 45 public List query_book(){ 46 try{ 47 String sql="select * from tb_book"; 48 ResultSet rs = jdbc.query(sql, null); 49 //结果处理 50 List<Book> books = new ArrayList<Book>(); 51 Book book = null; 52 //当一个对象是null时 ，调用方法或者属性 就会报空指针 53 while(rs.next()){ 54 book = new Book(); 55 book.setId(rs.getString(1)); 56 book.setName(rs.getString(2)); 57 book.setAuthor(rs.getString(3)); 58 book.setPublish(rs.getString(4)); 59 book.setPrice(rs.getDouble(5)); 60 book.setDes(rs.getString(6)); 61 books.add(book); 62 } 63 return books; 64 }catch(Exception e){ 65 e.printStackTrace(); 66 }finally { 67 jdbc.close(); 68 } 69 return null; 70 } 71 public boolean query_stmt(String username,String password) { 72 try{ 73 String sql="select * from tb_users where username='"+username+"' and password='"+password+"'"; 74 ResultSet rs = jdbc.query(sql, null); 75 //结果处理 76 if(rs.next()){ 77 return true; 78 } 79 }catch(Exception e){ 80 e.printStackTrace(); 81 }finally{ 82 jdbc.close(); 83 } 84 return false; 85 } 86 public boolean query(String username,String password) { 87 try{ 88 89 String sql="select * from tb_users where username=? and password=?"; 90 //执行sql语句 91 ResultSet rs = jdbc.query(sql, username,password); 92 //结果处理 93 if(rs.next()){ 94 return true; 95 } 96 }catch(Exception e){ 97 e.printStackTrace(); 98 }finally { 99 jdbc.close(); 100 } 101 return false; 102 } 103 }
tomcat的context.xml下修改datasource配置：
1 <Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource" 2 maxActive="100" maxIdle="20" maxWait="30000" username="root" password="root" 3 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" 4 removeAbandoned="true" removeAbandonedTimeout="30" />
简化后的代码：
1 import java.sql.Connection; 2 import java.sql.PreparedStatement; 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 6 import javax.naming.Context; 7 import javax.naming.InitialContext; 8 import javax.sql.DataSource; 9 10 public class JdbcUtil { 11 //private static String CLASSNAME="com.mysql.jdbc.Driver"; 12 //private static String URL="jdbc:mysql://localhost:3306/test"; 13 //private static String USERNAME="root"; 14 //private static String PASSWORD="root"; 15 private Connection conn=null; 16 private PreparedStatement pst=null; 17 private ResultSet rs=null; 18 /*static{ 19 try { 20 Class.forName(CLASSNAME); 21 } catch (ClassNotFoundException e) { 22 e.printStackTrace(); 23 } 24 }*/ 25 26 public Connection getConnection(){ 27 try { 28 //conn = DriverManager.getConnection(URL,USERNAME,PASSWORD); 29 Context context = new InitialContext(); 30 DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/test"); 31 conn = ds.getConnection(); 32 } catch (Exception e) { 33 e.printStackTrace(); 34 } 35 return conn; 36 } 37 38 public ResultSet query(String sql,Object... param){ 39 getConnection(); 40 try { 41 pst = conn.prepareStatement(sql); 42 if(param!=null){ 43 for(int i=0;i<param.length;i++){ 44 pst.setObject(i+1, param[i]); 45 } 46 } 47 rs = pst.executeQuery(); 48 return rs; 49 } catch (SQLException e) { 50 e.printStackTrace(); 51 } 52 return null; 53 } 54 public int update(String sql,Object... param){ 55 getConnection(); 56 try { 57 pst = conn.prepareStatement(sql); 58 if(param!=null){ 59 for(int i=0;i<param.length;i++){ 60 pst.setObject(i+1, param[i]); 61 } 62 } 63 int i = pst.executeUpdate(); 64 return i; 65 } catch (SQLException e) { 66 e.printStackTrace(); 67 } 68 return 0; 69 } 70 71 public void close(){ 72 try{ 73 if(conn!=null){ 74 conn.close(); 75 } 76 if(pst!=null){ 77 pst.close(); 78 } 79 if(rs!=null){ 80 rs.close(); 81 } 82 }catch(Exception e){ 83 e.printStackTrace(); 84 } 85 } 86 }
