eclipse可以在Java 中access-bridge-64.jar路径中添加自己的mysqljar包
index界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="t3.jsp">点击添加四本书</a><br>
<a href="t4.jsp">查询所有图书</a><br>
<a href="t5.jsp">查询张三写的书</a><br>
<a href="t6.jsp">查询所有Java相关的书籍</a><br>
<a href="t7.jsp">判断是否存在一本叫做《JSP程序设计》的书</a><br>
<a href="t8.jsp">查询清华大学出版社所出版的书</a><br>
<a href="t9.jsp">查询价格小于50元的书</a><br>
----------------------<br>
<a href="t10.jsp">点击添加四本书</a><br>
<a href="t11.jsp">以表格的形式显示所有用户</a><br>
通过用户名找密码<br>
<form action="t12.jsp" method="post">
用户名:<input type="text" name="uu"><br>
<input type="submit" value="找回密码">
</form>
<a href="t12.jsp">通过用户名找密码</a><br>
<a href="t13.jsp">通过等级查询用户</a><br>
<a href="t14.jsp">查找某用户是否存在</a><br>
<a href="t15.jsp">根据关键字模糊查询</a><br>
</body>
</html>
3、插入测试数据
001 Java程序设计 张三 34.00 清华大学出版社
002 Java编程圣经 李四 66.00 清华大学出版社
003 Python入门 王五 54.00 机械工业出版社
004 Java面试宝典 张三 42.00 人民邮电出版社
JDBC操作代码截图:
<%@page import= "java.sql.*" %>//导入关于所有关于sql语句的包
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql01 = "insert into book values('001','java程序设计','张三',34.00,'清华大学出版社')";
String sql02 = "insert into book values('002','java编程圣经','李四',66.00,'清华大学出版社')";
String sql03 = "insert into book values('003','Python入门','王五',54.00,'机械工业出版社')";
String sql04 = "insert into book values('004','java面试宝典','张三',42.00,'人民邮电出版社')";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
//选择要执行的SQL语句
PreparedStatement ptmt = conn.prepareStatement(sql01);
ptmt.execute();
ptmt = conn.prepareStatement(sql02);
ptmt.execute();
ptmt = conn.prepareStatement(sql03);
ptmt.execute();
ptmt = conn.prepareStatement(sql04);
ptmt.execute();
//5.关闭
conn.close();
out.println("添加成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
</body>
</html>
4、查询所有数据并在JSP显示
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where writer='张三'";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
//如果值看一行数据,用if;如果看多行数据用while
while(rs.next()){
out.print(rs.getString(3)+"写了《"+rs.getString(2)+"》,这本书"+rs.getDouble(4)+"元!<br>");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("查询成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
查询张三写的书
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where writer='张三'";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
//如果值看一行数据,用if;如果看多行数据用while
while(rs.next()){
out.print(rs.getString(3)+"写了《"+rs.getString(2)+"》,这本书"+rs.getDouble(4)+"元!<br>");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("查询成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
查询所有Java相关的书籍,并在JSP显示
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where bname like '%java%' ";//等于和like同级
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
//如果值看一行数据,用if;如果看多行数据用while
while(rs.next()){
out.print("有《"+rs.getString(2)+"》");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("查询成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
7、判断是否存在一本叫做《JSP程序设计》的书,JSP显示判断结果
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where bname='JSP程序设计'";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
if(rs.next()){
out.print("这本书存在!<br>");
}else{
out.print("这本书不存在!<br>");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("判断成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
8、查询清华大学出版社所出版的书
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where cds='清华大学出版社'";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
//如果值看一行数据,用if;如果看多行数据用while
while(rs.next()){
out.print("《"+rs.getString(2)+"》<br>");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("查询成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>
9、查询价格小于50元的书
JDBC操作代码截图:
<%
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/BookDB";
String sql = "select * from book where price<50";
try {
//1.加载驱动jkjhkkjkh
Class.forName("com.mysql.jdbc.Driver");
//2.输入账号、密码数据库名
Connection conn = DriverManager.getConnection(url,username,password);
PreparedStatement ptmt = conn.prepareStatement(sql);
//查询会查询出一张表,用结果集保存查询到的表
ResultSet rs = ptmt.executeQuery();//excecute是执行增删改的,executeQuery是执行查询的
//如果值看一行数据,用if;如果看多行数据用while
while(rs.next()){
out.print("《"+rs.getString(2)+"》<br>");
}
ptmt.execute();
//5.关闭
conn.close();
out.println("查询成功! <a href='index.jsp'>返回首页</a>");
} catch (Exception e) {
e.printStackTrace(); //输出错误信息
}
%>