Part 18th _ Database manipulation using Servlets, JSPs, and JDBC

Using Servlet, JSP and JDBC to implement database manipulation--jdbc connect MySQL instance:

• Put the MySQL database-driven package (my driver Mysql-connector-java-5.1.34-bin.jar, which needs to be downloaded separately) in the Web-inf->lib directory
• Modify the Server.xml under Conf in the Tomcat decompression directory to configure the current application context
• Create a new database MyDB (MySQL comes with a database named Test, so you can't use the name test)
• Create database table users, field: ID (primary key), Username,password,truename,birthday,registerdate,sex,interest,remark

ready to work : cmd->net start, check if MySQL does not start the Network service, manually start it.

Under command line:

• Mysql-uroot-proot
• Create DATABASE MyDB;
• Use MyDB;
• Show tables;

Create a table using Navicat

Navicat Use Raiders: Click [Connect], fill in a suitable connection name, then switch to the [Advanced] tab, before [using advanced connection], select a non-system database, fill in the user name and password.

With this visual tool, you can easily change the table structure and table data by clicking [Query]->[Create query] to execute the SQL statement.

Change font Size: Tools---options to change font properties.

Step into the topic: Create a new table named Users, field: ID (primary key), Username,password,truename,birthday,registerdate,sex,interest,remark, Set the appropriate type, the ID is int, the primary key cannot be empty (not null), set to Auto-increment, username, password, truename are varchar (20), here must not forget to specify the length, Otherwise, using JDBC to add information to the database thrown exception, directly inserted in the database record error can not be completed, birthday, registerdate are datetime,sex for tinyint,interest for varchar (20), Remark is text or Mediumtext.

View the DDL in the table message as follows:

CREATE TABLE ' users ' (  ' id ' int (one) ' NOT null auto_increment,  ' hh ' varchar (0) DEFAULT NULL,  ' username ' varchar Default NULL,  ' password ' varchar (default null, '  truename ' varchar) default NULL,  ' birthday ' DateTime default NULL,  ' registerdate ' datetime default NULL,  ' sex ' tinyint (4) default NULL,  ' interest ' varchar () default NULL,  ' remark ' Mediumtext,  PRIMARY KEY (' id ')) engine=innodb default Charset=utf8

To create a related class file:

Com.test.bean new class under Package: Users.java

Com.test.db new class under Package: Usersdb.java

New page under Web directory: register.jsp

New Servlet:processusersservlet under Com.test.servlet package

New filter under Com.test.filter: Userfilter.java (config in Web. xml)

Page created under Web directory: listallusers.jsp;listsingleuser.jsp

User information modification left to everyone to complete

Users.java under the Com.test.bean package:

Package Com.test.bean;import java.sql.date;public class users{private int id;private string Username;private string Password;private string truename;private Date birthday;private Date registerdate;private string Sex;private string interest;private String remark;public int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetUserName () {return username;} public void Setusername (String username) {this.username = username;} Public String GetPassword () {return password;} public void SetPassword (String password) {this.password = password;} Public String Gettruename () {return truename;} public void Settruename (String truename) {this.truename = Truename;} Public Date Getbirthday () {return birthday;} public void Setbirthday (Date birthday) {this.birthday = birthday;} Public Date Getregisterdate () {return registerdate;} public void Setregisterdate (Date registerdate) {this.registerdate = registerdate;} Public String Getsex () {return sex;} public void Setsex (String sex) {this.sex = sex;} Public STRing Getinterest () {return interest;} public void Setinterest (String interest) {this.interest = interest;} Public String Getremark () {return remark;} public void Setremark (String remark) {This.remark = remark;}}

Userdb.java under the Com.test.db package (key, database manipulation):

Package Com.test.db;import Java.io.inputstream;import Java.sql.connection;import java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.util.arraylist;import java.util.Properties; Import com.test.bean.users;/** * @author User * * TODO This class all methods to complete database operations related to the Users table */public class Usersdb{private String Res Ource = "Users.properties";p rivate properties props = null;//Get database connection private Connection getconnection () {try{props = new Pr Operties (); InputStream in = GetClass (). getResourceAsStream (Resource);p rops.load (in); String drivers = Props.getproperty ("Jdbc.drivers"); String url = props.getproperty ("Jdbc.url"); String username = props.getproperty ("Jdbc.username"); String Password = props.getproperty ("Jdbc.password"); Class.forName (drivers);//Load Database driver return drivermanager.getconnection (URL, username, password);} catch (Exception ex) {ex.printstacktrace (); SYSTEM.OUT.PRINTLN ("Connection database exception occurred:" + ex.getmessage ());} return null;} Insert a record public void save (Users Bean) {Try{connection con = this.getconnection (); String sql = "INSERT into users (Username,password,truename,birthday,registerdate,sex,interest,remark) VALUES (?,?,?,? ,?,?,?,?)"; PreparedStatement PS = con.preparestatement (sql);p s.setstring (1, Bean.getusername ());p s.setstring (2, Bean.getpassword ());p s.setstring (3, Bean.gettruename ());p s.setdate (4, Bean.getbirthday ());p s.setdate (5, Bean.getregisterdate ());p s.setstring (6, Bean.getsex ());p s.setstring (7, Bean.getinterest ());p s.setstring (8, Bean.getremark ());p s.executeupdate (); Complete the REAL database Insert Con.close ();} catch (Exception ex) {System.out.println ("Add record exception occurred:" + ex.getmessage () + "\ n");//Adjust the trial, I added the Ex.printstacktrace ();}} Update a record public void update (Users bean) {try{connection con = this.getconnection (); String sql = "Update users set password=?,truename=?,birthday=?,sex=?,interest=?,remark=?" where id=? "; PreparedStatement PS = con.preparestatement (sql);p s.setstring (1, Bean.getpassword ());p s.setstring (2, Bean.gettruename ());p s.setdate (3, Bean.getbirthday ());p s.setstring (4,Bean.getsex ());p s.setstring (5, Bean.getinterest ());p s.setstring (6, Bean.getremark ());p S.setint (7, Bean.getid ()); Ps.executeupdate ();//Real completion of Data update Con.close ();} catch (Exception ex) {System.out.println ("Modify record exception occurred:" + ex.getmessage ());}} Delete a record public void remove (int id) {Try{connection con = this.getconnection (); String sql = "Delete from users where id=?"; PreparedStatement PS = con.preparestatement (sql);p S.setint (1, id);p s.executeupdate (); Complete the real delete con.close ();} catch (Exception ex) {System.out.println ("Delete record exception occurred:" + ex.getmessage ());}} Query a record public Users restore (int id) {Users bean = null;try{connection con = this.getconnection (); String sql = "SELECT * from Users where id=?"; PreparedStatement PS = con.preparestatement (sql);p S.setint (1, id); ResultSet rs = Ps.executequery (); if (Rs.next ()) {bean = new Users (); Bean.setid (Rs.getint ("id")); Bean.setusername ( Rs.getstring ("username")), Bean.setpassword (rs.getstring ("password")), Bean.settruename (rs.getstring ("Truename") ); Bean.setbirthday (Rs.getdate ("BirthdAy ")); Bean.setregisterdate (Rs.getdate (" registerdate ")); Bean.setsex (rs.getstring (" Sex ")); Bean.setinterest ( Rs.getstring ("interest")); Bean.setremark (rs.getstring ("remark"));} Con.close ();} catch (Exception ex) {System.out.println ("Query record exception occurred:" + ex.getmessage ());} return bean;} Query all records public ArrayList getAllUsers () {ArrayList ArrayList = new ArrayList (); The Users bean = null;try{connection con = this.getconnection (); String sql = "SELECT * from users"; PreparedStatement PS = con.preparestatement (SQL); ResultSet rs = Ps.executequery (); while (Rs.next ()) {bean = new Users (); Bean.setid (Rs.getint ("id")); Bean.setusername ( Rs.getstring ("username")), Bean.setpassword (rs.getstring ("password")), Bean.settruename (rs.getstring ("Truename") ), Bean.setbirthday (Rs.getdate ("Birthday")), Bean.setregisterdate (Rs.getdate ("Registerdate")), Bean.setsex ( Rs.getstring ("Sex")), Bean.setinterest (rs.getstring ("interest")), Bean.setremark (rs.getstring ("remark")); Arraylist.add (bean);} Con.close ();} catch (Exception ex) {System.out.println ("queryAll record exceptions occur: "+ ex.getmessage ());} return arrayList;}}

Register.jsp under the Web directory:

<%@ page language= "java" pageencoding= "GB2312"%><! DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 transitional//en" >
 Processusersservlet under the Com.test.servlet package: 
 
Package Com.test.servlet;import Java.io.ioexception;import Java.text.parseexception;import Java.text.simpledateformat;import Java.util.arraylist;import Javax.servlet.servletexception;import Javax.servlet.http.httpservlet;import Javax.servlet.http.httpservletrequest;import Javax.servlet.http.httpservletresponse;import Com.test.bean.users;import Com.test.db.usersdb;public Class Processusersservlet extends httpservlet{protected void doget (httpservletrequest request,httpservletresponse response ) throws Servletexception, ioexception{//TODO Method stub generated by lombozprocess (request, response);} protected void DoPost (HttpServletRequest request,httpservletresponse response) throws Servletexception, ioexception{/ /TODO Method stub generated by lombozprocess (request, response);} protected void Process (HttpServletRequest request,httpservletresponse response) throws Servletexception, ioexception{ String type = Request.getparameter ("type"), if ("Save". Equals (Type)) {string username = Request.getpaRameter ("username"); String Password = request.getparameter ("password"); String truename = Request.getparameter ("Truename"); String birthday = Request.getparameter ("Birthday"); String sex = request.getparameter ("Sex"); String[] interest = request.getparametervalues ("interest"); String Remark = Request.getparameter ("remark"); java.sql.Date birthdaytodate = null; SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd"); Try{java.util.date Date = Sdf.parse (birthday); Birthdaytodate = new Java.sql.Date (Date.gettime ());} catch (ParseException e) {e.printstacktrace ();} String interesttostring = null; StringBuffer sb = new StringBuffer (); for (int i = 0; i < interest.length; i++) {sb.append (interest[i]);} interesttostring = Sb.tostring (); The Users bean = new users (); Bean.setusername (username); Bean.setpassword (password); bean.settruename (Truename); Bean.setinterest (interesttostring); bean.setsex (sex); Bean.setbirthday (birthdaytodate); Bean.setregisterdate (new Java.sql.Date (New Java.util.Date (). GetTime ())); Bean.setrEmark (remark); Usersdb UserDB = new Usersdb (); Userdb.save (bean); ArrayList ArrayList = Userdb.getallusers () request.setattribute ("All", ArrayList); Request.getrequestdispatcher ("/ Listallusers.jsp "). Forward (request, response);} if ("Remove". Equals (Type)) {String id = request.getparameter ("id"); Usersdb UserDB = new Usersdb (); Userdb.remove (Integer.parseint (id)); ArrayList ArrayList = Userdb.getallusers () request.setattribute ("All", ArrayList); Request.getrequestdispatcher ("/ Listallusers.jsp "). Forward (request, response);}}
 Filter under Com.test.filter: Userfilter.java 
 
Package Com.test.filter;import Java.io.ioexception;import Javax.servlet.filter;import javax.servlet.FilterChain; Import Javax.servlet.filterconfig;import Javax.servlet.servletexception;import javax.servlet.ServletRequest; Import javax.servlet.servletresponse;import javax.servlet.http.httpservletrequest;/** * @author User * * TODO To change the template for this generated type comment, go to window-preferences-Java-code style-code template */public class Usersfilter implements Filter{public void Init (FILTERCONFI G config) throws servletexception{//TODO Method stub generated by lomboz}public void DoFilter (ServletRequest request, Ser Vletresponse Response,filterchain chain) throws IOException, servletexception{//TODO Method stub generated by Lomboz (Ht tpservletrequest) request). Setcharacterencoding ("gb2312"); Chain.dofilter (request, response);} public void Destroy () {//Todo method stubs generated by Lomboz}public filterconfig Getfilterconfig () {//Todo method stub ge Nerated by Lombozreturn Null;} public void Setfilterconfig (filterconfig config) {//TODO MeThod stub generated by Lomboz}} 
Configure filters in Web. xml:
<filter> <filter-name>usersFilter</filter-name> <filter-class> com.test.filter.usersfilter</filter-class> </filter>  <filter-mapping> <filter-name> Usersfilter</filter-name> <url-pattern>/ProcessUsersServlet</url-pattern> </filter-mapping >
LISTALLUSERS.JSP:
<%@ page language= "java" pageencoding= "GB2312"%><%@ page import= " Java.util.*,com.test.bean.* "%><! DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 transitional//en" ><%arraylist ArrayList = (ArrayList) Request.getattribute ("All"); Users bean = Null;%><%@ page language= "java" pageencoding= "GB2312"%><%@ page import= "java.util.*,com.test.bean.*,com.test.db.* "%><%string id = request.getparameter (" id "); Usersdb usersdb = new Usersdb (); The Users bean = Usersdb.restore (integer.parseint (ID));%><! DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 transitional//en" >
Start the Tomcat server, type: http://localhost:8080/test/register.jsp in the browser, and the page appears:
Here are the pages that were registered two times, and all users are listed by default:
Click Lisi, the page appears:
Part 18th _ Database manipulation using Servlets, JSPs, and JDBC