Скачай конектор из репы мавена mysql JDBC connector
url = jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false
username = root
password = password
FAQ:
Q: Именованные параметры в JDBC ?
A: JDBC не поддерживает именованные параметры. Вот велосипед для этого Named Parameters for PreparedStatement
jsp
<!-- Bootstrap core CSS -->
<link href="lib/bootstrap.min.css" rel="stylesheet">
<link href="index.css" type="text/css" rel="stylesheet" media="all">
<%--prolog js--%>
<script src="index.js"></script>
<form action="" id="sendForm">
<input type="hidden" name="page" value="1">
<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Modal title</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<div class='filters'>
<%=pag.inputNumber("№ " ,"id","id", "=")%>
<%=pag.inputDate("Дата БД ФИАС с " ,"fias_date_from","fias_date", ">=")%>
<%=pag.inputDate("Дата БД ФИАС по " ,"fias_date_till","fias_date", "<=")%>
<%=pag.inputSelect("Кем выполнено обновление " ,"performer","performer")%>
<%=pag.inputSelect("Статус " ,"status","status")%>
<%=pag.inputText("Коментарий " , "comment","comment")%>
<div id="textinfo"></div>
<div class='clearfix'></div>
</div>
</div>
<div class="modal-footer">
<a type="button" class="btn btn-secondary" href="/ManualUpdate">Сбросить</a>
<button type="button" class="btn btn-secondary" data-dismiss="modal">Закрыть</button>
<button type="submit" class="btn btn-primary">Найти</button>
</div>
</div>
</div>
</div>
<table class="reqs">
<thead>
<tr>
<th></th>
<th>Статус</th>
<th>Дата обновления</th>
<th>Дата БД ФИАС</th>
<th>Файл</th>
<th>Способ обновления</th>
<th>Кем выполнено обновление</th>
<th>Комментарий</th>
</tr>
</thead>
<tbody>
<%
StringBuilder sbRes = new StringBuilder();
ResultSet rs = pag.getRes();
while (rs.next()){
String id =rs.getString("id");
String status =rs.getString("status");
Timestamp update_date = rs.getTimestamp("update_date");
Timestamp fias_date = rs.getTimestamp("fias_date");
String file =rs.getString("file");
String update_type =rs.getString("update_type");
String performer =rs.getString("performer");
String comment =rs.getString("comment");
sbRes.append("<tr>"+
"<td >"+id+"</td>"+
"<td >"+status+"</td>"+
"<td >"+pag.dateFormatYearAndTime(update_date.toString())+"</td>"+
"<td >"+pag.dateFormatYear(fias_date.toString())+"</td>"+
"<td >"+file+"</td>"+
"<td >"+update_type+"</td>"+
"<td >"+performer+"</td>"+
"<td >"+comment+"</td>"+
"</tr>" );
}
%>
<%=sbRes%>
</tbody>
</table>
<%=pag.getPaginator()%>
</form>
<script src="lib/jquery-3.2.1.slim.min.js"></script>
<script src="lib/bootstrap.min.js"></script>
css
function pageChange(page) {
$("[name=page]").val(page);
$("#sendForm").submit();
}
js
/*
paginator
*/
.pagination {
display: inline-block;
}
.pagination a {
color: black;
float: left;
padding: 8px 16px;
text-decoration: none;
}
.pagination a.active {
background-color: #4CAF50;
color: white;
}
.pagination a:hover:not(.active) {background-color: #ddd;}
class
package com.servlet;
import com.fias_connection.Connect;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
public class Paginator {
public StringBuilder like = new StringBuilder();
public HttpServletRequest req;
public int page;
StringBuilder paginator = new StringBuilder();
int countRes = 85;
int paginatorLimit =10;
Connect connect = new Connect();
Connection con = connect.getConnection();
PreparedStatement stmt = null;
public Paginator (HttpServletRequest request){
HttpSession session = request.getSession();
String str = request.getParameter( "bitrix_user" );
if (str != null && !str.equals("")){
session.setAttribute("bitrix_user",str);
}
req = request;
str = request.getParameter( "page" );
if (str != null && !str.equals("")){
page = Integer.parseInt(str);
if (page<1) page =1;
} else {
page =1;
}
req = request;
}
public String getLike() {
return like.toString();
}
public String df (String s)
{
String res = "";
if (s != null && !s.equals("")) {
try {
SimpleDateFormat in = new SimpleDateFormat("dd.MM.yyyy");
SimpleDateFormat out = new SimpleDateFormat("yyyy-MM-dd");
String str = out.format(in.parse(s));
res = str.toString();
} catch (Exception e) {
e.printStackTrace();
}
}
// System.out.println("df "+ s+" " +str);
return res;
}
public String moreLike(String par , String type , String operator) throws Exception
{
String str = req.getParameter( par );
if (str != null && !str.equals("")){
if (type.equals("int")){
like.append( " AND "+par+" "+operator+" "+str+"");
}
if (type.equals("str")){
// str = new String(str.getBytes("ISO8859_1"), "UTF-8");
like.append( " AND "+par+" "+operator+" '%"+str+"%'");
}
if (type.equals("date")){
like.append( " AND "+par+" "+operator+" '"+df(str)+"'");
}
return str;
}else {
return "";
}
}
public String inputText(String label, String name , String dbname)
{
String par = req.getParameter( name );
if (par != null && !par.equals("")) {
// str = new String(str.getBytes("ISO8859_1"), "UTF-8");
like.append(" AND " + dbname + " like '%" + par + "%'");
} else par = "";
String str="<div class='form-group '>\n" +
" <label for='"+name+"' class='control-label'>"+label+"</label>\n" +
" <div>\n" +
" <input class='form-control ' id='"+name+"' name='"+name+"' value='"+par+"' type='text'>\n" +
" </div>\n" +
"</div>\n" ;
return str;
}
public String inputNumber(String label, String name , String dbname , String operator)
{
String par = req.getParameter( name );
if (par != null && !par.equals("")) {
// str = new String(str.getBytes("ISO8859_1"), "UTF-8");
like.append(" AND " + dbname + " "+operator+" " + par + " ");
} else par = "";
String str="<div class='form-group '>\n" +
" <label for='"+name+"' class='control-label'>"+label+"</label>\n" +
" <div>\n" +
" <input class='form-control' id='"+name+"' name='"+name+"' value='"+par+"' type='number'>\n" +
" </div>\n" +
"</div>\n" ;
return str;
}
public String inputDate(String label, String name , String dbname , String operator)
{
String par = req.getParameter( name );
if (par != null && !par.equals("")) {
// str = new String(str.getBytes("ISO8859_1"), "UTF-8");
like.append(" AND " + dbname + " "+operator+" '" + par+ "' ");
} else par = "";
String str="<div class='form-group '>\n" +
" <label for='"+name+"' class='control-label'>"+label+"</label>\n" +
" <div>\n" +
" <input class='form-control' id='"+name+"' name='"+name+"' value='"+par+"' type='date'>\n" +
" </div>\n" +
"</div>\n" ;
return str;
}
public String inputSelect(String label, String name , String dbname) throws Exception
{
String par = req.getParameter( name );
if (par != null && !par.equals("") && !par.equals("-")) {
// str = new String(str.getBytes("ISO8859_1"), "UTF-8");
like.append(" AND " + dbname + " = '" + par + "' ");
} else par = "";
StringBuilder sb = new StringBuilder();
PreparedStatement stmt = null;
String sql = "select distinct ("+dbname+") from history; " ;
stmt = con.prepareStatement(sql);
// System.out.println(stmt );
ResultSet rs = stmt.executeQuery();
String datalast = "";
sb.append("<option>-</option>" ) ;
while (rs.next()){
datalast =rs.getString(dbname);
String sel = (datalast.equals(par) ) ? "selected" : "";
sb.append("<option "+sel+">" + datalast+ "</option>");
}
String str="<div class='form-group '>\n" +
" <label for='"+name+"' class='control-label'>"+label+"</label>\n" +
" <div>\n" +
" <select class='form-control' id='"+name+"' name='"+name+"' value='"+par+"'>\n" +
sb.toString()+
" </select>\n" +
" </div>\n" +
"</div>\n" ;
return str;
}
public String getLastDbUpdateDate() throws Exception{
SimpleDateFormat in = new SimpleDateFormat("dd.MM.yyyy");
SimpleDateFormat out = new SimpleDateFormat("yyyy-MM-dd");
String sql = "SELECT * FROM history h " +
" WHERE status = 'Активное' order by id desc LIMIT 1 ; " ;
stmt = con.prepareStatement(sql);
// System.out.println(stmt );
ResultSet rs = stmt.executeQuery();
String datalast = "";
while (rs.next()){
datalast =rs.getString("fias_date");
}
return in.format(out.parse(datalast) );
}
public String dateFormatYear(String str) throws Exception{
SimpleDateFormat in = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat out = new SimpleDateFormat("dd.MM.yyyy");
return out.format(in.parse(str) );
}
public String dateFormatYearAndTime(String str) throws Exception{
SimpleDateFormat in = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat out = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
return out.format(in.parse(str) );
}
public static List resultSetToArrayList(ResultSet rs) throws SQLException
{
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
ArrayList list = new ArrayList(50);
while (rs.next()){
HashMap row = new HashMap(columns);
for(int i=1; i<=columns; ++i){
row.put(md.getColumnName(i),rs.getObject(i));
}
list.add(row);
}
return list;
}
public ResultSet getRes ()
{
try {
String sql = "SELECT * " +
"FROM history h " +
"WHERE h.id > 0 "+ like + " " +
"order by id desc "+
"LIMIT "+paginatorLimit+" OFFSET "+ (page-1)*paginatorLimit +" ";
stmt = con.prepareStatement(sql);
System.out.println(stmt );
ResultSet rs = stmt.executeQuery();
return rs;
}catch (Exception e) {
e.printStackTrace();
//System.out.println("asd");
}
return null;
}
public String printPageByNumber (int num , int count){
if (num==-1) return ("\n<a class=''>..</a>");
String active;
if (num==count) active= "active"; else active="";
return ("\n<a onclick='pageChange("+count+")' class='"+active+"'>"+count+"</a>");
}
public String getPaginator() throws Exception
{
String sql = "SELECT count(*) " +
"FROM history h " +
"WHERE id > 0 "+ like + " ; " ;
stmt = con.prepareStatement(sql);
// System.out.println(stmt );
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
countRes = Integer.parseInt( rs.getString("count"));
}
paginator.append( "<div class='pagination'>");
paginator.append("<a onclick='pageChange(\""+ (page-1)+"\")' > «</a>" ) ;
paginator.append(printPageByNumber(page , 1 )) ;
int limit = (int) Math.ceil(countRes /paginatorLimit)+1 ;
if (countRes < paginatorLimit*10 ){
for (int i=2 ; i<limit ; i++){
paginator.append(printPageByNumber(page , i ) );
}
if (limit!=1 && limit!=0 )
paginator.append(printPageByNumber(page , limit ) ) ;
} else {
if (page < 5) {
for (int i=2 ; i<9 ; i++){
paginator.append(printPageByNumber(page , i )) ;
}
paginator.append(printPageByNumber(-1 , -3 )) ;
} else {
paginator.append(printPageByNumber(-1 , -3 ) );
for (int i=page-3 ; i<page ; i++){
paginator.append(printPageByNumber(page , i ) ) ;
}
paginator.append(printPageByNumber(page , page ) ) ;
for (int i=page+1 ; i< page+4 ; i++){
if (i < limit)
paginator.append(printPageByNumber(page , i ) );
}
if (page+4 < limit)
paginator.append(printPageByNumber(-1 , -3 ) );
}
if (limit != page)
paginator.append(printPageByNumber(page , limit ) );
}
paginator.append( "<a onclick='pageChange(\""+(page+1) + "\")'>»</a>" );
paginator.append( "Всего: "+ countRes );
paginator.append( "</div> " );
return paginator.toString();
}
public static void main(String[] args)
{
String like ="" ;
}
}