新手程序员工作日志7

MYSQL的搭建

安装中MySQL Sener Instance configuration Wizard界面

选择第三个并使character变为utf-8

1 删除数据库之后在安装必须删除注册文件否则无法安装

2Mysql 数据库的常用命令及命令的格式。

3navicat for Mysql 软件的应用

2 tomcat的安装预配置 jdbc驱动的使用

localhost：8080打不开---------------------是因为端口号被占用

3Myesclips的配置

最新版的已经自带tomcat

window---perfence----Myesclips-----servers-----RuntimeEnviroments----add（到这来添加合适的版本）

4 数据库的增删改查

insert。jsp 添加

query.jsp 查询数据库

update.jsp 更新

delete。jsp 删除

5 数据库的乱码

两种方式乱码

1 数据库的格式上传格式是错的导致相应的乱码

2 未给相宜的数据库（容器） jsp页面服务器添加统一的编码格式

。

jsp 页面实现数据库简单的增删改查

需求：

name

Password

Gender

去实现增删改查

1.创建数据库

mysql>create database school;

mysql>use school

mysql>create table school(

->id int(30) not null primary key,

->name varchar(50),

->password varchar30),

->gender varchar(30),

->);

创建了库名为school 表名为school

2 新建web 项目

步骤 new - web project-webRoot-新建jsp文件

Index.jsp

此页面是项目起始的界面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>Welcome,home</title>

<!--

-->

</head>

<a href="addStuInfo.jsp">点此添加信息</a><br/><br/>//各个页面的链接首个页面

<a href="showInfo.jsp">点此查询信息</a><br/><br/>

<a href="showInfo.jsp">点此修改信息</a><br/><br/>

<a href="showInfo.jsp">点此显示信息</a><br/><br/>

<br>

</center>

</body>

</html>

addStuInfo.Jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>输入信息界面</title>

<!--

-->

function validate() //调用此函数来获取用户在该页面添加的信息的值

{

var id = document.forms[0].id.value;

var name = document.forms[0].name.value;

var password = document.forms[0].password.value;

if(id <= 0){

alert("学号不能为空，请输入学号！");

return false;

}

else if(name.length <= 0){

alert("姓名不能为空，请输入姓名！");

return false;

}

else if (password.length<= 0){

alert("请输入密码！");

return false;

}

else{

return true;

}

//document.getElementById("form").submit();

}

</script>

</head>

<body background="pic/1.jpg"> //背景图片

<br>

//form表单的形式去提交数据提交按钮使用validate函数去执行获取值的方法

<h4> 性别：<input type="radio" name="gender" value="男">男

</form> // 在这里去将这4 个数据在以表单的形式去传递信息。

<a href="showInfo.jsp">查询所有信息</a>

</center>

</body>

</html>

showInfo.jsp

1、pageEncoding="UTF-8"的作用是设置JSP编译成Servlet时使用的编码。
2、contentType="text/html;charset=UTF-8"的作用是指定对服务器响应进行重新编码的编码。
3、request.setCharacterEncoding("UTF-8")的作用是设置对客户端请求进行重新编码的编码。
4、response.setCharacterEncoding("UTF-8")的作用是指定对服务器响应进行重新编码的编码。

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<!--

-->

</head>

response.setCharacterEncoding("UTF-8"); //对服务器请求重新编码的编码

request.setCharacterEncoding("UTF-8");//对客户端请求进行重新编码的编码

;//是用来获取URL中的参数

String id = request.getParameter("id")

String name = request.getParameter("name");

String password = request.getParameter("password");

String gender = request.getParameter("gender");

Connection conn = null; 声明数据库连接对象

Connection con=null; 声明对象变量，后面指向一个DriverManager.getConnection(url,"root",""); 的对象引用

先定义null ，再指向对象的引用，是因为3楼的代码方式，因为try catch 的原因。
如果直接将异常抛出的话，那可以直接
Connection con = DriverManager.getConnection(url,"root","");

Statement stat = null;

ResultSet rs = null;

1、这个是jdbc里面的经典语法了
2、通常和Connection 、 Preparedstatement等共同使用
3、首先用ResultSet rs = null；表示定义出这个ResultSet的对象rs
4、可以在后续的使用中来给rs赋值或者其他的操作

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

传统的使用jdbc来访问数据库的流程为：
Class.forName(“com.mysql.jdbc.Driver”);
String url = “jdbc:mysql://localhost:3306/test?user=root&password=123456″;
Connection con = DriverManager.getConnection(url);
Statement statement = con.createStatement();

rs = stat.executeQuery("select * from school");

数据库命令查寻在school表中。

<br>

<br>

<h3>全部信息如下</h3>

<tr>

</tr>

//将指针从当前位置下移一行。ResultSet 指针最初位于第一行之前；第一次调用 next 方法使第一行成为当前行；第二次调用使第二行成为当前行，依此类推。如果如果新的当前行有效，则返回 true；如果不存在下一行，则返回 false

while(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

这个是执行SQL语句带参数的写法

value后面的问号就代表你前面字段的值！

<td><a href="delete.jsp?id=<%=rs.getInt("id") %>">删除</a></td> 删除相应id的值

out.print("</tr>");

}

</table>

<br>

Form表单对id来进行查找。

<h3>按id查询:<input type="text" name="id" value="" title="id不能为空" ></input>

<br>

</form>

Form表单对姓命来进行查找。

<h3>按姓名查询:<input type="text" name="name" value="" title="姓名不能为空"></input>

<br>

</form>

Form表单对性别来进行查找。

<h3> 按性别查询:<input type="text" name="gender" value="" title="性别不能为空"></input>

<br>

</form>

<br>

<h3><a href=addInfo.jsp>返回添加信息页面</a></h3>

<br>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close(); //这里如果conn没有赋值为空则会编译报错

conn = null;

}

</body>

</html>

Delete.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8"); 请求是客户端的重新编码

String id = request.getParameter("id");

//通过id对数据进行删除。在这里获取url的参数

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school"; //数据库的名字

String user = "root"; //用户的名字

String passwords = "123456"; //用户的密码

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

stat.executeUpdate("delete from school where id = " + id + "");

更改用id从数据库中删除

rs = stat.executeQuery("select * from school");

查寻的数据库命令

if(rs.next())

{

out.print("<center><br><br><h3>删除成功！</h3></center>");

}

else{

out.print("<center><h3>删除失败！</h3></center>");

}

<br>

<center> <a href=addInfo.jsp>返回添加信息页面</a> <a href=showInfo.jsp>返回信息查询页面</a></center>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

Update.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<!--

-->

function validate()

{

var id = document.forms[0].id.value;

var name = document.forms[0].name.value;

var password = document.forms[0].password.value;

if(id <= 0){

alert("学号不能为空，请输入学号！");

return false;

}

else if(name.length <= 0){

alert("姓名不能为空，请输入姓名！");

return false;

}

else if(password.length<= 0){

alert("密码不能为空");

return false;

}

else{

return true;

}

//document.getElementById("form").submit();

}

</script>

</head>

response.setCharacterEncoding("UTF-8");

request.setCharacterEncoding("UTF-8");

String id = request.getParameter("id");

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

rs = stat.executeQuery("select * from school where id=" + id + "");

更新修改也用id 去实现

<br>

<br>

<h3>要修改的学生信息如下</h3>

<tr>

</tr>

while(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

out.print("</tr>");

</table>

<br>

<h3>将学生信息更改为：</h3>

<h4> id：<input type="text" name="id" value="<%=rs.getInt("id") %>" title="id不能改变" readonly="readonly"></input><br></h4>

<h4> 性别：<input type="radio" name="gender" value="男">男

</form>

<br>

<h3><a href=addInfo.jsp>返回添加信息页面</a></h3>

<h3><a href=showInfo.jsp>返回信息查询页面</a></h3>

}

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

updateShow.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8");

String id1 = request.getParameter("id");

String name1 = request.getParameter("name");

String password1 = request.getParameter("password");

String gender1 = request.getParameter("gender");

Connection conn = null;

Statement stat = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

stat.execute("update school set id=" + id1 + ",name='" + name1 + "',password='" + password1+ "',gender='" + gender1 + "' where id=" + id1 + "");

ResultSet rs = stat.executeQuery("select * from school where id=" + id1 + "");

传输给服务器的格式很重要 (“update school set id=”+id1+ “”,name=’ ”+name1+””);

<br>

<br>

<h3>修改后的信息为：</h3>

<hr>

<br>

<tr>

</tr>

while(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

out.print("</tr>");

}

</table>

<br>

<h3><a href=addInfo.jsp>返回添加信息页面</a></h3>

<h3><a href=showInfo.jsp>返回信息查询页面</a></h3>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

select_for_gender.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>按性别查询</title>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8");

String gender = request.getParameter("gender");

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

rs = stat.executeQuery("select * from school where gender='" + gender + "'");

//("select * from school where gender='" + gender + "'")经典的一句即

（“select *from school where gender=‘“+gender+”’ “）

<br>

<h3>符合条件的信息</h3><hr>

<br>

<tr>

</tr>

while(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

out.print("</tr>");

}

</table>

<br>

<h4><a href=showInfo.jsp>返回查询页面</a></h4>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

select_for_id.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>按id查询</title>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8");

String id = request.getParameter("id");

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

rs = stat.executeQuery("select * from school where id=" + id + ""); //这是表名

<br>

<h3>符合条件的学生信息</h3><hr>

<br>

<tr>

</tr>

if(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

out.print("</tr>");

}

else{

out.print("<h4>不存在此条件的信息！</h4>");

}

</table>

<br>

<h4><a href=showInfo.jsp>返回查询页面</a></h4>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

select_for_name.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>按姓名查询</title>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8");

String name = request.getParameter("name");

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url="jdbc:mysql://localhost:3306/school";

String user = "root";

String passwords= "123456";

conn = DriverManager.getConnection(url,user,passwords);

stat = conn.createStatement();

rs = stat.executeQuery("select * from school where name='" + name + "'");

<br>

<h3>符合条件的学生信息</h3><hr>

<br>

<tr>

</tr>

while(rs.next())

{

out.print("<tr>");

out.print("<td>" + rs.getInt("id") + "</td>");

out.print("<td>" + rs.getString("name") + "</td>");

out.print("<td>" + rs.getString("password") + "</td>");

out.print("<td>" + rs.getString("gender") + "</td>");

out.print("</tr>");

}

</table>

<br>

<h4><a href=showInfo.jsp>返回查询页面</a></h4>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>

Insert.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page import="java.sql.*"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<!--

-->

</head>

request.setCharacterEncoding("UTF-8");

String id = request.getParameter("id");

String name = request.getParameter("name");

System.out.println(name);

String password = request.getParameter("password");

String gender = request.getParameter("gender");

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/school"; //数据库名

String user = "root";

String passwords = "123456";

conn = DriverManager.getConnection(url, user, passwords);

stat = conn.createStatement();

String sql = "insert into school(id,name,password,gender) values(" + id + ",'" + name + "','" + password + "','" + gender + "')";

在这里添加信息int型的跟String型的不是同样的格式。

stat.executeUpdate(sql);

rs = stat.executeQuery("select * from school");

if(rs.next())

{

out.print("<br><h3>成功输入！</h3>");

}

else{

out.print("<br><h3>输入失败！</h3>");

}

<br>

<a href=addInfo.jsp>返回添加信息页面</a> <a href=showInfo.jsp>进入信息查询页面</a> <a href=index.jsp>返回起始页面</a>

</center>

if(rs != null)

{

rs.close();

rs = null;

}

if(stat != null)

{

stat.close();

stat = null;

}

if(conn != null)

{

conn.close();

conn = null;

}

</body>

</html>