WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

JSP connection database


This tutorial assumes that you already know how JDBC applications work. Before you start learning JSP database access, please visit Java MySQL connection to set up related drivers and configurations.

Note:

You can download the jar package provided on this site: mysql-connector-java-5.1.39-bin.jar

After downloading, copy mysql-connector-java-5.1.39-bin.jar to the lib directory under tomcat.

Starting from the basic concepts, let's create a simple table and create a few records in the table.


Create test data

Next we create a welookups database in MySQL and create a website data table with the following table structure:

  CREATE TABLE   `websites`     ( 
    `id`     int   (  11  )   NOT NULL AUTO_INCREMENT  ,  
    `name`     char   (  20  )   NOT NULL DEFAULT   ''   COMMENT   'site name'  ,  
    `url`   varchar   (  255  )   NOT NULL DEFAULT   ''  ,  
    `alexa`     int   (  11  )   NOT NULL DEFAULT   '0'   COMMENT   'Alexa ranking'   ,  
    `country`     char   (  10  )   NOT NULL DEFAULT   ''   COMMENT   'Country'  ,  
  PRIMARY KEY   (  `id`  )  
 )   ENGINE   =   InnoDB   AUTO_INCREMENT   =   10   DEFAULT CHARSET   =   utf8  ;  

Insert some data:

INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', 'Taobao', 'https://www.taobao.com/', '13', 'CN'), ('3', 'Tutorial', 'http://www.welookups.com', '5892', ''), ('4', 'Faint', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

SELECT operating

The next example shows how to use JSTL SQL tags to run SQL SELECT statements:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
 
<html>
<head>
<title>SELECT operating</title>
</head>
<body>
<!--
JDBC driver name and database URL
Database username and password, need to be set according to your own
useUnicode=true&characterEncoding = utf-8 prevents garbled Chinese
 -->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/welookups?useUnicode=true&characterEncoding=utf-8"
     user="root"  password="123456"/>
 
<sql:query dataSource="${snapshot}" var="result">
SELECT * from websites;
</sql:query>
<h1>JSP Database instance-look tutorial</h1>
<table border="1" width="100%">
<tr>
   <th>ID</th>
   <th>Site name</th>
   <th>Site address</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
   <td><c:out value="${row.id}"/></td>
   <td><c:out value="${row.name}"/></td>
   <td><c:out value="${row.url}"/></td>
</tr>
</c:forEach>
</table>
 
</body>
</html>

INSERT operating

This example tells us how to use JSTL SQL tags to run SQL INSERT statements:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
 
<html>
<head>
<title>SELECT operating</title>
</head>
<body>
<!--
JDBC driver name and database URL
Database username and password, need to be set according to your own
useUnicode =true&characterEncoding = utf-8 prevents garbled Chinese
 -->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/welookups?useUnicode=true&characterEncoding=utf-8"
     user="root"  password="123456"/>
<!--
Insert data
 -->
<sql:update dataSource="${snapshot}" var="result">
INSERT INTO websites (name,url,alexa,country) VALUES ('Tutorial Mobile Station', 'http://m.welookups.com', 5093, 'CN');
</sql:update>
<sql:query dataSource="${snapshot}" var="result">
SELECT * from websites;
</sql:query>
<h1>JSP Database instance-look tutorial</h1>
<table border="1" width="100%">
<tr>
   <th>ID</th>
   <th>Site name</th>
   <th>Site address</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
   <td><c:out value="${row.id}"/></td>
   <td><c:out value="${row.name}"/></td>
   <td><c:out value="${row.url}"/></td>
</tr>
</c:forEach>
</table>
 
</body>
</html>

DELETE operation

This example tells us how to use JSTL SQL tags to run SQL DELETE statements:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
 
<html>
<head>
<title>SELECT operating</title>
</head>
<body>
<!--
JDBC driver name and database URL
Database username and password, need to be set according to your own
useUnicode=true&characterEncoding = utf-8 prevents garbled Chinese
 -->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/welookups?useUnicode=true&characterEncoding=utf-8"
     user="root"  password="123456"/>

<!--
Delete data with ID 11
 -->
<sql:update dataSource="${snapshot}" var="count">
  DELETE FROM websites WHERE Id = ?
  <sql:param value="${11}" />
</sql:update>

<sql:query dataSource="${snapshot}" var="result">
SELECT * from websites;
</sql:query>
<h1>JSP Database instance-look tutorial</h1>
<table border="1" width="100%">
<tr>
   <th>ID</th>
   <th>Site name</th>
   <th>Site address</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
   <td><c:out value="${row.id}"/></td>
   <td><c:out value="${row.name}"/></td>
   <td><c:out value="${row.url}"/></td>
</tr>
</c:forEach>
</table>
 
</body>
</html>

This example shows how to use JSTL SQL tags to run SQL UPDATE statements:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
 
<html>
<head>
<title>SELECT operating</title>
</head>
<body>
<!--
JDBC driver name and database URL
Database username and password, need to be set according to your own
useUnicode=true&characterEncoding = utf-8 prevents garbled Chinese
 -->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/welookups?useUnicode=true&characterEncoding=utf-8"
     user="root"  password="123456"/>

<!--
Change the name to ID 3: Tutorial changed to welookups
 -->
<c:set var="SiteId" value="3"/>
 
<sql:update dataSource="${snapshot}" var="count">
  UPDATE websites SET name = 'welookups' WHERE Id = ?
  <sql:param value="${SiteId}" />
</sql:update>

<sql:query dataSource="${snapshot}" var="result">
SELECT * from websites;
</sql:query>
<h1>JSP Database instance-look tutorial</h1>
<table border="1" width="100%">
<tr>
   <th>ID</th>
   <th>Site name</th>
   <th>Site address</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
   <td><c:out value="${row.id}"/></td>
   <td><c:out value="${row.name}"/></td>
   <td><c:out value="${row.url}"/></td>
</tr>
</c:forEach>
</table>
 
</body>
</html>