以访问数据库的方式用jsp动态生成下拉列表-简单MVC模型

曾经终败给现在 2023-07-21 11:27 44阅读 0赞

## 以访问数据库的方式用jsp动态生成下拉列表 ##

图片效果：  
![在这里插入图片描述][20200403195419408.png]  
建好数据库，建表并插入数据：  
`CREATE TABLE optionlist( id INT, employee VARCHAR(20) ); INSERT INTO optionlist(id,employee) VALUES (1,"java模块的前世今生"), (2,"java从入坟到出土"), (3,"我要java的半生缘"), (4,"如果不是真的喜欢谁学java"), (5,"C++我的兄弟"), (6,"java从贫困到欠债");`

建立User类，即传说中的Model层：

public class User{ 
    	private int id;
    	private String employee;
    	public int getId() { 
    		return id;
    	}
    	public void setId(int id) { 
    		this.id = id;
    	}
    	public String getEmployee() { 
    		return employee;
    	}
    	public void setEmployee(String employee) { 
    		this.employee = employee;
    	}
    	public User(int id, String employee) { 
    		super();
    		this.id = id;
    		this.employee = employee;
    	}
    }

在同一包下建立Control层，即DAO（data access object：数据访问层）

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    public class Employee { 
    	private User user=null;
    	Connection b=null;
    	PreparedStatement v=null;
    	ArrayList<User> Al=null;
    	public final ArrayList<User> QueryToAll(){ 
    		Al=new ArrayList<User>();
    		try { 
    			//导入驱动java-mysql-jar包
    		   //注册驱动
    		   Class.forName("com.mysql.cj.jdbc.Driver");
    		   //获取数据库连接对象
    		   
    			b = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?serverTimezone=GMT","root","123qwe");
    		 //定义sql语句
    			System.out.println(b.isClosed());
    		   String sql="select id,employee from optionlist;";
    		   //获取执行sql的对象Statement
    		   v=b.prepareStatement(sql);
    		   //执行sql
    		   ResultSet rs=v.executeQuery();
    		   while(rs.next()){ 
    			   int id=rs.getInt(1);
    			   String employee=rs.getString(2);
    			   user=new User(id,employee);
    			   Al.add(user);
    		   }
    		   } catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (ClassNotFoundException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally { 
    			if(v!=null){ 
    			try { 
    				v.close();
    			} catch (SQLException e) { 
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}}
    			if(b!=null){ 
    				try { 
    					b.close();
    				} catch (SQLException e) { 
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		return Al;
    	}
    	
    }

建立View层，即jsp文件视图：

<%@ page import="java.util.List" %>
    <%@ page import="com.luo.test.*" %>
    <%@ page import="com.luo.test.User" %>
    <%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert title here</title>
    </head>
    <body>
    <table height="150">
    		<tr>
    			<td align="center" colspan="4">员工信息查询</td>
    		</tr>
    		<tr>
    			<td>员工姓名：<input type="text" name="name"></td>
    			<td>年龄：<input type="text" name="age"></td>
    			<td>
    				<select>
    					<% for(int i=0;i<list.size();i++){  %>
    					<option><%=list.get(i).getEmployee() %></option>
    					<%} %>
    				</select>
    			</td>
    			<td>
    				<input type="submit" name="提交" value="提交">
    			</td>
    		</tr>
    	</table>
    	<%!
    	 List<User> list=new Employee().QueryToAll();
    	%>
    	
    </body>
    </html>

[20200403195419408.png]: /images/20230528/862f21c374594403b1910b32b13d4f18.png