jsp 连接sql数据库查询（源代码）

最新推荐文章于 2023-04-05 10:19:00 发布

weixin_34090562

最新推荐文章于 2023-04-05 10:19:00 发布

阅读量345

点赞数

文章标签：数据库 java

原文链接：https://my.oschina.net/durong/blog/84227

版权

为什么80%的码农都做不了架构师？>>>

数据库结构

建立连接

package Test;

import java.sql.*;

public class ConnectionManager {
private static final String DRIVER_CLASS="com.microsoft.sqlserver.jdbc.SQLServerDriver";   //定义常量（下同）
private static final String DATABASE="jdbc:sqlserver://localhost:1433;DatabaseName=news";
private static final String DATABASE_USER = "sa";
private static final String DATAVSES_PASS = "123456";

public static Connection getConnection(){
  Connection conn = null;
  try {
   Class.forName(DRIVER_CLASS);
   conn = DriverManager.getConnection(DATABASE,DATABASE_USER,DATAVSES_PASS);

  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
}
public static void clossConnection (Connection connection){   //释放资源（下同）
  try {
   if (connection !=null && !connection.isClosed()){
    connection.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }
}
public static void closeResultSet(ResultSet rs){
  try {
   if (rs!=null){
    rs.close();
    rs = null ;
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
}
public static void closeStatement(PreparedStatement pstmt) {
  try {
   if (pstmt != null){
    pstmt.close();
    pstmt = null;
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
}
}

建立实体类

package Test;

import java.util.Date;

public class FirestLevelTitle {
private int id ; // ID
private String titleName; //名称
private String creator ; //创建者
private Date createTime ;//创建时间
public FirestLevelTitle(int id ,String titleName ,String creator,Date createTime){
  this.id = id;
  this.titleName = titleName;
  this.creator = creator;
  this.createTime = createTime;
}
public Date getCreateTime() {
  return createTime;
}
public void setCreateTime(Date createTime) {
  this.createTime = createTime;
}
public String getCreator() {
  return creator;
}
public void setCreator(String creator) {
  this.creator = creator;
}
public int getId() {
  return id;
}
public void setId(int id) {
  this.id = id;
}
public String getTitleName() {
  return titleName;
}
public void setTitleName(String titleName) {
  this.titleName = titleName;
}

}
通过jdbc 访问数据库

package Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FirestLeveTitleDbOpereation {

public List getAllFirestLeveTitleList(){   //返回FirestLevelTitle对象的集合
  List list = new ArrayList();     //定义数组
  Connection conn = null ;
  PreparedStatement pstmt = null ;
  ResultSet rs = null ;
  try {
   conn = ConnectionManager.getConnection();   //建立连接
   String sql = "select * from FirstLevelTitle order by CreatorTime desc"; //sql语句
   pstmt = conn.prepareStatement(sql);   //PreparedStatement 语句
   rs = pstmt.executeQuery(); //查询结果集
   while (rs.next()) {      //处理结果
    int id = rs.getInt("id");
    String title = rs.getString("TitleName");
    String createw = rs.getString("Creator");
    Date time = rs.getDate("CreatorTime");

   //把个属性封装到FirestLevelTitle 对象中
    FirestLevelTitle fTitle = new FirestLevelTitle(id,title,createw,time);
    list.add(fTitle);    //添加到集合中
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  finally{   //释放资源
     ConnectionManager.closeResultSet(rs);
   ConnectionManager.closeStatement(pstmt);

   ConnectionManager.clossConnection(conn);

  }
  return list;
}
}

jsp页面

<%@ page language="java" pageEncoding="GBK"
import="java.util.* ,Test.*" //添加引用

%>
<html>
<head>
</head>

<body>
<%
  FirestLeveTitleDbOpereation first = new FirestLeveTitleDbOpereation();
  List list = first.getAllFirestLeveTitleList();
  if (list.size()!=0){
   for(int i = 0;i <list.size();i++){ //循环遍历
    FirestLevelTitle fTitle = (FirestLevelTitle)list.get(i);
    %>
    <td>
     表名是： <%=fTitle.getTitleName()%> <br/>
      id是：<%=fTitle.getId() %><br/>
      时间是：<%=fTitle.getCreateTime() %><br/>
    </td>
    <%
   }
  }
%>

</body>
</html>