一、了解数据库的基本知识
- 1>:Driver接口:java.sql.Driver是所有JDBC驱动程序需要实现的接口,这个接口提供给不同的数据库厂商,他们使用的接口名不同。
-:SQLserver的JDBC驱动的类名:“com.microsoft.sqlserver.jdbc.SQLServerDriver”
-:Oracle的JDBC驱动的类名:“oracle.jdbc.driver.OracleDriver”
-:MySQL的JDBC驱动的类名:“com.mysql.jdbc.Driver”
-
2>加载与注册JDBC驱动
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);该语句用于加载
-
3>JDBC连接数据库的URL表现形式通常分为三个部分(通常用:分开):
1:协议:在JDBC中唯一允许的协议只能为jdbc.
2:子协议:子协议用于标识一个数据库驱动程序
3:子名称:具体看下面
-:连接SQLserver: “jdbc:sqlserver://localhost:1433;DatabaseName=user”
-:连接Oracle: “jdbc:thin:@localhost:1521:ORCL”
-:连接MySQL: “jdbc:mysql://localhost:3306/databasename”
-
4>执行SQL语句接口Statement对象,该对象的常用方法:
-:void close() 关闭释放资源
-:ResultSet executeQuery(String sql):执行某条查询语句并返回结果
-:int execulteUpdate(String sql):可以执行insert,undate或者delete语句
二、创建一个类(文件位于src)
package com.ll;
public class Furniture {
private int id;
private String name;
private int price;
private int num;
private String dates;
private String style;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getDates() {
return dates;
}
public void setDates(String dates) {
this.dates = dates;
}
public String getStyle() {
return style;
}
public void setStyle(String style) {
this.style = style;
}
}
四、dao层与数据库连接的部分(文件位于src)
package dao;
import com.Furniture;
import java.sql.*;
import java.util.ArrayList;
public class FurnitureDao {
public ArrayList queryAllFurniture() throws Exception {
//实现数据库的访问
Connection connection=null;
ArrayList fu=new ArrayList();
//创建连接
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("1111驱动异常1111");
e.printStackTrace();
}
//加载驱动
String conStr="jdbc:mysql://localhost:3306/java12?characterEncoding=utf8&useSSL=false; DatabaseName=java12";
//配置连接字符串
String user="root";
//配置用户名
String password="root";
//配置用户的访问密码
connection= DriverManager.getConnection(conStr,user,password);
//创建数据库连接对象
//查询语句
String sql = "select * from furnitures; ";
Statement statement=connection.createStatement();
//返回结果集
ResultSet resultSet=statement.executeQuery(sql);
while(resultSet.next())
{
Furniture furniture= new Furniture();
furniture.setId(resultSet.getInt(1));
furniture.setName(resultSet.getString(2));
furniture.setPrice(resultSet.getInt(3));
furniture.setNum(resultSet.getInt(4));
furniture.setDates(resultSet.getString(5));
furniture.setStyle(resultSet.getString(6));
fu.add(furniture);
}
resultSet.close();
statement.close();
connection.close();
return fu;
}
}
五、重写index.jsp文件
<%@ page contentType="text/html;charset=GB2312" language="java" %>
<%@page import="dao.FurnitureDao" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="com.Furniture" %>
<%@ page import="com.Furniture" %>
<html>
<head>
<title>数据库连接显示界面</title>
<style type="text/css">
#body {
background-color: #FFD2BD;
}
</style>
</head>
<body id="body">
<h1>家具信息如下图所示:</h1>
<%
FurnitureDao furnDao = new FurnitureDao();
ArrayList furn = furnDao.queryAllFurniture();
%>
<table border="1">
<tr>
<td>编号</td>
<td>名称</td>
<td>价格</td>
<td>数量</td>
<td>日期</td>
<td>风格</td>
</tr>
<%
for(int i=0;i<furn.size();i++) {
Furniture f1 = (Furniture) furn.get(i);
%>
<tr>
<td><%=f1.getId()%></td>
<td><%=f1.getName()%></td>
<td><%=f1.getPrice()%></td>
<td><%=f1.getNum()%></td>
<td><%=f1.getDates()%></td>
<td><%=f1.getStyle()%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
</body>
</html>
六、前提是创建数据库和表如下图:
七、在浏览器打开的样式