数据库第一次课堂作业
数据库代码：
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.print.attribute.standard.RequestingUserName;
import jdk.nashorn.internal.ir.GetSplitState;
import jdk.nashorn.internal.runtime.linker.NashornBeansLinker;
import jdk.nashorn.internal.runtime.linker.NashornBeansLinker;
public class DBUtil {
//数据库URL和账号密码
public static final String connectionURL="jdbc:mysql://localhost:3306/new_schem1?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
public static final String username="root";
public static final String password="yq1476942590";
static Connection connection;
static ResultSet rSet;
static PreparedStatement sql;
//数据库连接
public static Connection getConnection()
{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(connectionURL, username, password);
} catch (Exception e) {
// TODO: handle exception
System.out.println("数据库连接失败");
e.printStackTrace();
}
return null;
} //数据库连接
public static boolean getUsername() //遍历获取数据库中的内容
{
try {
connection=getConnection();
sql= connection.prepareStatement("select * from new_table");
rSet=sql.executeQuery();
//System.out.println("yhjyuki");
while(rSet.next())
{
System.out.println(rSet.getString(1)+" "+rSet.getString(2)+" "+rSet.getString(3)+" "+rSet.getString(4)+" "+rSet.getString(5)+" "+rSet.getString(6)+" "+rSet.getString(7)+" "+rSet.getString(8)+" "+rSet.getString(9)+" "+rSet.getString(10)+" "+rSet.getString(11)+" "+rSet.getString(12));
}
return true;
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
public static boolean addUsername(String user,String psw, String username,String number,String sex,String year,String school,String major,String class1,String email,String region,String intro)//增添
{
try {
connection=getConnection();
sql =connection.prepareStatement("insert into new_table (user,psw,sex,username,number,email,school,major,class,year,region,intro) values(\'"+user+"\',\'"+psw+"\',\'"+sex+"\',\'"+username+"\',\'"+number+"\',\'"+email+"\',\'"+school+"\',\'"+major+"\',\'"+class1+"\',\'"+year+"\',\'"+region+"\',\'"+intro+"\')");
sql.executeUpdate();
} catch (SQLException e) {
// TODO 自动生成的 catch
e.printStackTrace();
}
return false;
}
public static void main(String[] args) {
getUsername();
//addUsername();
//getUsername();
}
}
//数据库URL和账号密码
public static final String connectionURL="jdbc:mysql://localhost:3306/new_schem1?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
public static final String username="root";
public static final String password="yq1476942590";
static Connection connection;
static ResultSet rSet;
static PreparedStatement sql;
//数据库连接
public static Connection getConnection()
{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(connectionURL, username, password);
} catch (Exception e) {
// TODO: handle exception
System.out.println("数据库连接失败");
e.printStackTrace();
}
return null;
} //数据库连接
public static boolean getUsername() //遍历获取数据库中的内容
{
try {
connection=getConnection();
sql= connection.prepareStatement("select * from new_table");
rSet=sql.executeQuery();
//System.out.println("yhjyuki");
while(rSet.next())
{
System.out.println(rSet.getString(1)+" "+rSet.getString(2)+" "+rSet.getString(3)+" "+rSet.getString(4)+" "+rSet.getString(5)+" "+rSet.getString(6)+" "+rSet.getString(7)+" "+rSet.getString(8)+" "+rSet.getString(9)+" "+rSet.getString(10)+" "+rSet.getString(11)+" "+rSet.getString(12));
}
return true;
}
catch(SQLException e)
{
e.printStackTrace();
}
return false;
}
public static boolean addUsername(String user,String psw, String username,String number,String sex,String year,String school,String major,String class1,String email,String region,String intro)//增添
{
try {
connection=getConnection();
sql =connection.prepareStatement("insert into new_table (user,psw,sex,username,number,email,school,major,class,year,region,intro) values(\'"+user+"\',\'"+psw+"\',\'"+sex+"\',\'"+username+"\',\'"+number+"\',\'"+email+"\',\'"+school+"\',\'"+major+"\',\'"+class1+"\',\'"+year+"\',\'"+region+"\',\'"+intro+"\')");
sql.executeUpdate();
} catch (SQLException e) {
// TODO 自动生成的 catch
e.printStackTrace();
}
return false;
}
public static void main(String[] args) {
getUsername();
//addUsername();
//getUsername();
}
}
servlet代码
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import util.DBUtil;
/**
* Servlet implementation class registS
*/
@WebServlet("/registS")
public class registS extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");//可以使用中文
String user=request.getParameter("user");
String psw=request.getParameter("psw");
String sex=request.getParameter("sex");
String username =request.getParameter("username");
String number =request.getParameter("number");
String email=request.getParameter("email");
String school=request.getParameter("school");
String major=request.getParameter("major");
String class1=request.getParameter("class");
String year=request.getParameter("year");
String region=request.getParameter("region");
String intro=request.getParameter("intro");
// System.out.println(user+" "+psw+" "+username+" "+sex+" "+year+" "+school+" "+major+" "+class1+" "+email+" "+region+" "+intro+" ");
DBUtil.addUsername(user,psw,username,number,sex,year,school,major,class1,email,region,intro);//增添
}
* Servlet implementation class registS
*/
@WebServlet("/registS")
public class registS extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");//可以使用中文
String user=request.getParameter("user");
String psw=request.getParameter("psw");
String sex=request.getParameter("sex");
String username =request.getParameter("username");
String number =request.getParameter("number");
String email=request.getParameter("email");
String school=request.getParameter("school");
String major=request.getParameter("major");
String class1=request.getParameter("class");
String year=request.getParameter("year");
String region=request.getParameter("region");
String intro=request.getParameter("intro");
// System.out.println(user+" "+psw+" "+username+" "+sex+" "+year+" "+school+" "+major+" "+class1+" "+email+" "+region+" "+intro+" ");
DBUtil.addUsername(user,psw,username,number,sex,year,school,major,class1,email,region,intro);//增添
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
jsp代码
<%@ page language="java" contentType="text/html;charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<div class="dhdh"><div class="dngw">添加学生信息系统</div></div>
<hr size="5" noshade="noshade"/>
<form action="registS" method="post">
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<div class="dhdh"><div class="dngw">添加学生信息系统</div></div>
<hr size="5" noshade="noshade"/>
<form action="registS" method="post">
登录账号:<input name="user" type="text" id="number1" maxlength="10"><br>
登陆密码:<input name="psw" type="text" id="number2" maxlength="10"><br>
性别：<select name="sex" >
<option>男</option>
<option>女</option>
</select><br>
姓名:<input name="username" type="text" id="number3" maxlength="10"><br>
登陆密码:<input name="psw" type="text" id="number2" maxlength="10"><br>
性别：<select name="sex" >
<option>男</option>
<option>女</option>
</select><br>
姓名:<input name="username" type="text" id="number3" maxlength="10"><br>
学号:<input name="number" type="text" id="number4" maxlength="10"><br>
电子邮件:<input name="email" type="text" id="number5" maxlength="10"><br>
所在学校:<input name="school" type="text" id="number6" maxlength="10"><br>
所在系:<input name="major" type="text" id="number7" maxlength="10"><br>
所在班级:<input name="class" type="text" id="number8" maxlength="10"><br>
入学年份:<select name="year">
<option>1995</option>
<option>1996</option>
<option>1997</option>
<option>1998</option>
<option>1999</option>
<option>2000</option>
</select><br>
生源地:<input name="region" type="text" id="number9" maxlength="10"><br>
备注:<textarea name="intro" cols="30" rows="10" wrap="hard"></textarea><br>
<input name="submit" type="submit" class="btn_grey" value="添加"><br>
</form>
<script>
function checkForm(){
var uValue = document.getElementById("number1").value;
if(!/^[a-zA-Z][a-zA-Z0-9_]{5,11}$/.test(uValue)){
alert("用户名格式不正确!由六到十二英文字符、数字、下划线组成，以英文字母开头");
return false;
}
var pValue = document.getElementById("number2").value;
if(!/^[a-zA-Z0-9]{8,16}$/.test(pValue)){
alert("密码格式不正确!由八位以上英文和数字组成");
return false;
}
var nValue = document.getElementById("number4").value;
if(!/^[2][0][1][8][0-9]{4}$/.test(nValue)){
alert("学号格式不正确!");
return false;
}
var eValue = document.getElementById("number5").value;
if(!/^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+/.test(eValue)){
alert("邮箱格式不正确!");
return false;
}
}
</script>
</body>
</html>
电子邮件:<input name="email" type="text" id="number5" maxlength="10"><br>
所在学校:<input name="school" type="text" id="number6" maxlength="10"><br>
所在系:<input name="major" type="text" id="number7" maxlength="10"><br>
所在班级:<input name="class" type="text" id="number8" maxlength="10"><br>
入学年份:<select name="year">
<option>1995</option>
<option>1996</option>
<option>1997</option>
<option>1998</option>
<option>1999</option>
<option>2000</option>
</select><br>
生源地:<input name="region" type="text" id="number9" maxlength="10"><br>
备注:<textarea name="intro" cols="30" rows="10" wrap="hard"></textarea><br>
<input name="submit" type="submit" class="btn_grey" value="添加"><br>
</form>
<script>
function checkForm(){
var uValue = document.getElementById("number1").value;
if(!/^[a-zA-Z][a-zA-Z0-9_]{5,11}$/.test(uValue)){
alert("用户名格式不正确!由六到十二英文字符、数字、下划线组成，以英文字母开头");
return false;
}
var pValue = document.getElementById("number2").value;
if(!/^[a-zA-Z0-9]{8,16}$/.test(pValue)){
alert("密码格式不正确!由八位以上英文和数字组成");
return false;
}
var nValue = document.getElementById("number4").value;
if(!/^[2][0][1][8][0-9]{4}$/.test(nValue)){
alert("学号格式不正确!");
return false;
}
var eValue = document.getElementById("number5").value;
if(!/^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+/.test(eValue)){
alert("邮箱格式不正确!");
return false;
}
}
</script>
</body>
</html>
在此次实验中是自己第一次使用数据库，虽然没有在老师规定的时间里做完，但还是取得了不小的收获，明白了数据库如何连接以及调用，以及jsp技术，虽然掌握还不太熟练，但是已经可以慢慢的做出一些东西
