- 首先,先在MySQL数据库里面建一张表,类容包括id,age,name。注意最好不要写name,写成sname等非关键字的,要不然对后续操作会有影响。
- 然后java链接数据库(jdbc)链接的时候jdbc是小写,我连的时候首字母大写连不上。先接jdbc,然后再加载路径,密码账号,最后关掉链接,为其他用户的访问让路,让效率最大化
package com.z.zyy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Jdbc {
private static String driver="com.mysql.jdbc.Driver";//J要小写,不然连不上
private static String url="jdbc:mysql://localhost:3306/test";
private static String user="root";
private static String pwd="";
//连jdbc
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
//加载
public static Connection getCon() throws SQLException{
return DriverManager.getConnection(url,user,pwd);
}
//关闭
public static void close(ResultSet rs,Statement st,Connection con){
try {
if(rs!=null) rs.close();
} catch (SQLException e) {
throw new RuntimeException();
}finally{
try {
if(st!=null) st.close();
} catch (SQLException e) {
throw new RuntimeException();
}finally{
try {
if(con!=null) con.close();
} catch (SQLException e) {
throw new RuntimeException();
}
}
}
}
}
- 链接完了之后,就可以写javabean了,这也是一个类,里面的属性必须私有化,保护数据安全,且数据类型对应就是数据库表头的数据类型。必须提供共有的get和set来访问属性,必须实现Serializable接口。最后,自己写的类必须重写tostring方法。
package com.z.zyy;
import java.io.Serializable;
public class JaveBean implements Serializable{
private int id;
private int age;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "JaveBean [id=" + id + ", age=" + age + ", name=" + name + "]";
}
}
- 之后,就可以在Dao中写增删改查及分页等方法了。
package com.z.zyy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Dao {
Connection con;
PreparedStatement ps;
ResultSet rs;
//增加
public void add(JaveBean jb){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("insert into student values(null,?,?)");
ps.setInt(1, jb.getAge());
ps.setString(2, jb.getName());
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
Jdbc.close(null, ps, con);
}
}
//删除
public void del(int id){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("delete from student where id=?");
ps.setInt(1,id);
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
Jdbc.close(null, ps, con);
}
}
//修改
public void upd(JaveBean jb){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("update student set age=? where id=?");
ps.setInt(1, jb.getAge());
ps.setInt(2, jb.getId());
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
Jdbc.close(null, ps, con);
}
}
//根据id查一个
public void getone(int id){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("select * from student where id=?");
ps.setInt(1, id);
rs=ps.executeQuery();
//****************************************
JaveBean bb=new JaveBean();
if(rs.next()){
bb.setId(rs.getInt(1));
bb.setAge(rs.getInt(2));
bb.setName(rs.getString(3));
}
//*************************************
}
catch (SQLException e) {
throw new RuntimeException();
}finally{
Jdbc.close(rs, ps, con);
}
}
//查全部
public List<JaveBean> getAll(){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("select * from student");
rs=ps.executeQuery();
List<JaveBean> list=new ArrayList<JaveBean>();
while(rs.next()){
JaveBean bb=new JaveBean();
bb.setId(rs.getInt(1));
bb.setAge(rs.getInt(2));
bb.setName(rs.getString(3));
list.add(bb);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
Jdbc.close(rs, ps, con);
}
}
//分页
public List<JaveBean> getSomeBanji(int page){
try {
con=Jdbc.getCon();
ps=con.prepareStatement("select * from student limit ?,10");
ps.setInt(1, (page-1)*10);
rs=ps.executeQuery();
List<JaveBean> list=new ArrayList<JaveBean>();
while(rs.next()){
JaveBean bb=new JaveBean();
bb.setId(rs.getInt(1));
bb.setAge(rs.getInt(2));
bb.setName(rs.getString(3));
list.add(bb);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
Jdbc.close(rs, ps, con);
}
}
}
- 做主显示页面,用户可以通过网络路径找到这个界面对数据进行操作。而这个页面也算是对用户开放的唯一页面
document.location 这个对象包含了当前URL的信息
location.host 获取port号
location.hostname 设置或获取主机名称
location.href 设置或获取整个URL
location.port设置或获取URL的端口号
<%@page import="com.z.zyy.JaveBean"%>
<%@page import="com.z.zyy.Dao"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE>
<html>
<head>
</head>
<body>
<h1>球员信息管理</h1>
<%
Dao dao=new Dao();
//获取有多少行,方便后面分页
int count=dao.getAll().size();
//分页
int maxpage=(int)Math.ceil((count/10.0));
String str=request.getParameter("page");
int p=1;
if(str!=null){
try{
p=Integer.parseInt(str);
}catch(Exception e){
p=1;
}
}
if(p<=0) p=1;//小于最小页数
if(p>=maxpage) p=maxpage;//大于最大页数
List<JaveBean> lists=dao.getSomeBanji(p);
%>
<table width="900" border="1" align="center">
<tr>
<th>编号</th>
<th>年龄</th>
<th>姓名</th>
<th>操作</th>
</tr>
<%
for(JaveBean b:lists){
%>
<tr align="center">
<td><%=b.getId() %></td>
<td><%=b.getAge() %></td>
<td><%=b.getName() %></td>
<td>
<button onclick="confirm('你确定要删除吗')==true?window.location='dodel.jsp?id=<%=b.getId() %>':window.location='biao.jsp'">删除</button>
/ <!-- 这里用到三目运算,如果弹出框点确定,则删除,如果按取消,则还在当前页面。window.location代表页面跳转 -->
<button onclick="window.location='findone.jsp?id=<%=b.getId() %>'">修改</button>
</td>
</tr>
<%
}
%>
</table>
<div style=" text-align: center">
<button onclick="window.location='biao.jsp?page=1'">首页</button>
<button onclick="window.location='biao.jsp?page=<%=p-1%>'">上一页</button>
<font color="red" size="7"><%=p %></font>/<%=maxpage %>//显示总共几页,当前在第几页
<button onclick="window.location='biao.jsp?page=<%=p+1%>'">下一页</button>
<button onclick="window.location='biao.jsp?page=<%=maxpage%>'">末页</button>
<form action="biao.jsp" style="display: inline-block">
<input oninput="f1(this)" id="in" name="page" size="1" maxlength="2" /><button>go</button>
</form>
<script type="text/javascript">
function f1(e){
if(isNaN(e.value)){
e.value="";
}
}
</script>
</div>
</body>
</html>
- 如果在主页面点击删除,则需要跳转到删除界面,删除完毕之后,需要再次跳转到主页面。而且删除是根据主键id删除,所以跳转需要把参数带过去
<%@page import="com.z.zyy.Dao"%>
<%@page import="com.z.zyy.JaveBean"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE >
<html>
<head>
</head>
<body>
<%
String bb=request.getParameter("id");//获取id
int id=0;
if(bb!=null){
id=Integer.parseInt(bb);
}
Dao dao=new Dao();
dao.del(id);
response.sendRedirect("biao.jsp"); //重定向到主页面
%>
</body>
</html>
- 增加
<%@page import="com.z.zyy.Dao"%>
<%@page import="com.z.zyy.JaveBean"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE >
<html>
<head>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String age=request.getParameter("age");
String name=request.getParameter("name");
int i=Integer.parseInt(age);//String强转int
JaveBean bb=new JaveBean();
bb.setAge(i);
bb.setName(name);
Dao dao=new Dao();
dao.add(bb);
response.sendRedirect("biao.jsp");
%>
</body>
</html>