🗄️

[DB]DB機能の実装:SQL(Select/Insert/Update/Delete)

2022/07/01に公開

SQL

◆はじめに

テキストボックスに入力されて、送信されたテキストを

DB送信文字列として画面に返送するアプリケーションです。

そして、「select」「insert」「update」「delete」ボタンで

データベースにデータを送信したり、呼び出したりできます。

(1)「Select」・・・データベースに登録されていた文字を表示

(2)「Insert」・・・文字列をデータベースに登録

(3)「Update」・・・「Updateテキストデータ」という文字列にデータを更新

(4)「Delete」・・・データベースに登録されているデータを全て削除

◆SQLとは

「Structured Query Language」の略で、直訳すると「構造化問い合わせ言語」

リレーショナルデータベース(RDB)のデータを操作するための言語です。

大きく分けて、「select」「insert」「update」「delete」の四種類があります。

SQL文は、コンピュータ言語のひとつですが、プログラミング言語ではありません。

なので、Eclipseなどで扱う場合は、Stringの型でデータベースへ送ります。

そして、処理はデータベースで行います。

また、データベースにアクセスするためには、

データベースの種類によって異なるライブラリが必要になります。

本記事では、HSQLDBを扱います。

◆前提/環境など

本記事は、以下の記事の「InputFunction」プロジェクトをベースに作成します。

Echo機能のServletの処理時間を計測するFilterを実装しています。

また、Sessionで結果を保持するようになっています。

以下の記事の内容を踏まえた前提で、記事は書いていますのでご了承ください。

仕様は以下です。

<Echo機能> ・文字列を入力し、送信ボタンを押すと、送信した文字列が画面に表示される。

<Validation機能> ・送信された文字列が空欄の場合は,「この項目は必須入力です。」と文字列が表示される。

<TimeFiletr機能(Listener)> ・Servletの処理時間を計測し、コンソールに表示

<Session機能> ・処理結果をSessionがリセットされるまで保持する

※本記事では、 eclipseは以下のバージョンでの画像を使用しています。

日本語化はしていません。

他のversionでの動作は確認しておりません。

◆手順

それでは、実装していきます。

まず、データベースを用意して、次にソースコードを記述していきます。

最後に動作確認をしたら終了です。

①データベース

(1)データベース作成

まず、基本的なところは下記の記事よりお願いします。

その上で、本記事のアプリケーション用に、

DBの構築を行っていきたいと思います。

・ターミナルからDB作成と起動

・下記のディレクトリで、これ以降のコマンドをターミナルに打ちます。

cd ~(配置場所)/hsqldb-2.4.0/hsqldb/lib

・DB名は「textdb」

※変更したい方は、下記コマンドの該当箇所を変更し、これ以降も読み替えてください。

java -cp hsqldb.jar org.hsqldb.Server -database.0 file:textdb -dbname.0 textdb

すると、データベースが起動します。

先程のターミナルは、そのまま開いた状態にしておき、

新規で、別のターミナルを開きます。

こちらから、DBの管理マネージャを開きテーブルを作成します。

java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

アクセス画面で、下記の通り入力します。

jdbc:hsqldb:hsql://localhost/mydb/xdb

※hsqldbの後ろに、「hsql」を書き忘れないように注意しましょう

テーブル名は、「textdata」、パラメータは「text」のみにしました。

・以下のSQL文で、textdataテーブルをつくる

CREATE TABLE textdata
(
 TEXT LONGVARCHAR NOT NULL
)

・試しに、Insert文でデータをついかして、

Select文で確認してみましょう

INSERT INTO textdata (
  text
) VALUES (
  'テキストデータ'
)

SELECT *
FROM textdata

以上で、データベースの作成は完了です。

この後、エクリプスからデータベースに接続するのですが、

こちらのデータベース管理ソフトで接続していると繋がらないので、

ウィンドウを閉じて、接続を切ります。

また反対に、エクリプスで接続している場合は、

管理ソフトで接続できないので、注意しましょう。

(2)Eclipseで接続するためのドライバ

Eclipseと接続する際に、

JAVAの場合は、「JDBC」という仕組みを使用してアクセスします。

そのため、HSQLDB用のJDBCドライバが必要です。

JDBCドライバは、jarファイルでインストールしたHSQLDBのフォルダ内にあります

＜HSQLDBのディレクトリ/lib/hsqldb.jar＞

「hsqldb.jar」があったら、

・Eclipseのプロジェクトのlibフォルダへコピー

・ClassPathを通す

クラスパスの通し方は、ライブラリの場合と一緒ですので以下を参考にして下さい。

(3)Eclipseで接続するための情報

JAVAで接続する際に、

必要なパラメータは以下です。

String driver = "org.hsqldb.jdbcDriver";
String url = "jdbc:hsqldb:hsql://localhost/mydb/textdb";
final String user = "SA";
String password = "";

また、以下の項目をimportします

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

そして、今回はプロジェクトに、

DBAccessorというサーブレットのクラスを作成し、

その中で、SQL文を接続先のDBへ渡す処理を行っています。

それでは、具体的なコードは次を見てください。

②ソースコード

・プロジェクトのディレクトリ階層

・DBAccessor.javaのソースコード

DBAccessor.java

package dbaccessor;

import java.util.ArrayList;
import java.io.IOException;

import javax.security.auth.message.callback.PrivateKeyCallback.Request;
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.http.HttpSession;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;



//対応するjspのFormのaction名
  @WebServlet(urlPatterns = {"/dbaccess"})  
  
//Validation機能を持たせたEchoサーブレットクラス
public class DBAccessor extends HttpServlet{
	private static final long serialVersionUID = 1L;
	
	private static final String driver = "org.hsqldb.jdbcDriver";
	private static final String url = "jdbc:hsqldb:hsql://localhost/mydb/textdb";
	private static final String user = "SA";
	private static final String password = "";
		
		
//画面からPostされた場合に起動するメインとなるメソッド
	@Override
	protected void doPost(HttpServletRequest request , HttpServletResponse response)throws ServletException,
IOException {
		
		String name = request.getParameter("SubmitType");
		
		switch(name) { 
		    case "select":
		    	try {
					Class.forName(driver);
					Connection	connection = DriverManager.getConnection(url,user,password);
					connection.setAutoCommit(false);
					select(connection,request);
					connection.commit();
					connection.close();
					
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				break;
				
		    case "insert":
		    	try {
					Class.forName(driver);
					Connection	connection = DriverManager.getConnection(url,user,password);
					connection.setAutoCommit(false);
					insert(connection,request);
					connection.commit();
					connection.close();
					
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				break;
				
		    case "update":
		    	try {
					Class.forName(driver);
					Connection	connection = DriverManager.getConnection(url,user,password);
					connection.setAutoCommit(false);
					update(connection,request);
					connection.commit();
					connection.close();
					
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				break;
				
		    case "delete":
		    	try {
					Class.forName(driver);
					Connection	connection = DriverManager.getConnection(url,user,password);
					connection.setAutoCommit(false);
					delete(connection,request);
					connection.commit();
					connection.close();
					
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				break;
				
			default:
				//それ以外の処理
				System.out.println("error");
				break;
		}
		
		//リダイレクト
   //コンテキストルート（InputFunction）の取得
		String contextPath = getServletContext().getContextPath();
		//リダイレクトpathを設定（表示させるページ→echo.jsp）
		String path = contextPath + "/echo.jsp";
		//実際にリダイレクトを行っている関数
		response.sendRedirect(path);
	}

  
  //Select
  protected void select(Connection connection,HttpServletRequest request) throws SQLException {
	  Statement statement = connection.createStatement();	  
	  String sql = "SELECT * FROM textdata";
	  ResultSet rs = statement.executeQuery(sql);  
	  HttpSession session = request.getSession();  
	  ArrayList<String> textlist = new ArrayList<String>();	  
	  int count = 0;  
	  
	  try {
	  System.out.println("SELECTの結果（ここから）");  
		//画面に返すテキストをセット
	  while(rs.next()) {
		  String Text = rs.getString("text");
		  System.out.println(Text);	
		  textlist.add(Text);	 			
			count ++ ;
	  }  
	   　　　session.setAttribute("SQLmessage","読込成功"); 
		session.setAttribute("Resultmessage",textlist);  	
	  　　　
	  if(count == 0) {
		  session.setAttribute("Resultmessage","「登録されているテキストデータはありません」");  	
	  }  
	  
	  }catch(RuntimeException e) {
		  session.setAttribute("SQLmessage","読込失敗");
			System.out.println("読込失敗");
	  }
	  
	  System.out.println("SELECTの結果（ここまで）");
	  rs.close();
	  statement.close();	  
  }
  
  //Insert
  protected void insert(Connection connection,HttpServletRequest request) throws SQLException {
	  Statement statement = connection.createStatement();  
	  HttpSession session = request.getSession();   
	 try{
	  String inserttext = session.getAttribute("message").toString();  
		 
	  String sql = "INSERT INTO textdata (text) VALUES('" +inserttext + "')";
	  int updateCount = statement.executeUpdate(sql);
	  
	  if (updateCount == 1) {
		  session.setAttribute("SQLmessage","登録成功");
		  System.out.println("登録成功");
	  }   else {
		  session.setAttribute("SQLmessage","登録失敗");
		  System.out.println("登録失敗");
	  }
	} catch (RuntimeException e) {
		session.setAttribute("SQLmessage","登録失敗");
		System.out.println("登録失敗");
		statement.close();
		return;
	}
	  statement.close();
  }
  
  //Update
  protected void update(Connection connection,HttpServletRequest request) throws SQLException {
	  Statement statement = connection.createStatement();  
	  HttpSession session = request.getSession();  
	  try {
		  String updatetext = session.getAttribute("message").toString();
		  
		  String sql = "UPDATE textdata SET text = 'Updateテキストデータ' WHERE text = '"+updatetext+"'";
		  int updateCount = statement.executeUpdate(sql);	
	  
		  if (updateCount == 1) {
			  session.setAttribute("SQLmessage","更新成功");
			  System.out.println("更新成功");
		  }  else {
			  session.setAttribute("SQLmessage","更新失敗");
			  System.out.println("更新失敗");
		  }	  
	  } catch (RuntimeException e) {
		  session.setAttribute("SQLmessage","更新失敗");
		  System.out.println("更新失敗");
		  statement.close();
		  return;
	  }
	  	statement.close();
  }
  
  //Delete
  protected void delete(Connection connection , HttpServletRequest request) throws SQLException {
	  Statement statement = connection.createStatement();
	  String sql = "DELETE FROM textdata ";
	  HttpSession session = request.getSession();  
	  int updateCount = statement.executeUpdate(sql);
	  
	  if (updateCount >= 1) {
		  session.setAttribute("SQLmessage","削除成功");
		  System.out.println("削除成功");
	  } else {
		  session.setAttribute("SQLmessage","削除失敗");
		  System.out.println("削除失敗");
	  }  
	  statement.close();
  }
}

・jspのソースコード

echo.jsp

<%@page language="java" contentType = "text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page isELIgnored= "false" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<title>Echo機能＋Validation機能+TimeFilter機能(Listener)+Session機能+リロード対策＋DB機能</title>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>

<body>
	
 <h3>Echo機能＋Validation機能+TimeFilter機能(Listener)+Session機能+リロード対策+DB機能</h3>
 
   <%-- リセットのリクエストパラメータを渡す--%>
 <a href="echo?reset">セッションのリセット</a>

  <%-- 入力フォーム --%>
 <form action="echo" method="post">
  <br />入力してください　: 
  
  <%-- テキストボックス(m) --%>
  <input type="text" name="m" />
  
   <%-- Validationメッセージの出力  --%>
   <%-- var="vm"　→ c:out で　valueにセットする際に使う名称   --%>
   <%-- [' ']には、 MessageForm.javaのテキストボックスのメッセージ保持用のパラメータ名を入れる   --%>
 <c:forEach items="${validationMessage['textMessage']}" var="vm">
 　　<c:out value="${vm}"/>
 </c:forEach>
 
<br />
 
 <%-- 送信ボタン --%>
  <input type="submit" /> <br />

 </form>
 
 <br />
 
 
  <%-- Echoメッセージの出力--%>
DB送信文字列　:
 <c:if test="${ not empty message }">
「${ message }」です。
 </c:if>
 
 
  <br />
  
   <%-- DBAccessフォーム --%>
 <form action="dbaccess" method="post" name="DataBase" onsubmit="return SQL()" >

  <%-- Selectボタン --%>
<input type="submit" value="Select" onclick="DataBase.SubmitType.value='select'"  />
  <%-- Insertボタン --%>
<input type="submit" value="Insert" onclick="DataBase.SubmitType.value='insert'"  />
  <%-- Updateボタン --%>
<input type="submit" value="Update" onclick="DataBase.SubmitType.value='update'"  />
  <%-- Deleteボタン --%>
<input type="submit" value="Delete" onclick="DataBase.SubmitType.value='delete'"  />

 <c:if test="${ not empty SQLmessage }">
「${ SQLmessage }」
 </c:if>

<input name="SubmitType" type="hidden" value="" />
  
 </form>
 
  <br />
 
   <%-- SQL結果の出力--%>
 <c:if test="${ not empty Resultmessage }">
　 ${ Resultmessage }
 </c:if>
 
    <%-- SQL結果の出力--%>
 <script type="text/javascript">
function SQL() {
	 if (DataBase.SubmitType.value == 'select') {
	    }
	 if (DataBase.SubmitType.value == 'insert') {
	    }
	 if (DataBase.SubmitType.value == 'update') {
	    }
	 if (DataBase.SubmitType.value == 'delete') {
	    }
	    return true;
}
</script>

</body>

DB機能に関係するコードは以上です。

それ以外のファイルのコードなどは、

別の記事をご覧ください。

③動作確認

実装ができたら

・Tomcatを起動

・以下のURLにアクセス

以下の画面が表示されます

それでは、各SQL文を実行してみましょう。

(1)Select

「select」ボタンを押すと、

データベースに登録されているデータが表示されます。

以下のSQL文を実行後は、「select」ボタンをおしてデータを確認しています。

(2)Insert

・テキストボックスに「テキストデータ」と入力

・「insert」ボタンを押す

・つづけて、「テキストデータ2」「テキストデータ3」と入力してInsert

・「select」ボタンを押してデータを表示

下記のように表示されたら、OKです。

(3)Update

・テキストボックスに「テキストデータ2」と入力

・「update」ボタンを押す

・「Updateテキストデータ」に更新

・「select」ボタンを押してデータを表示

「テキストデータ2」が、

「Updateテキストデータ」という文字列に更新されていたらOKです。

(4)Delete

・「delete」ボタンを押す

・「select」ボタンを押してデータを表示

データベースに登録されているデータが全て削除されていると、

「登録されているテキストデータがありません」と表示されます。

◆さいごに

以上です。いかがだったでしょうか。

最後まで、ご覧いただきありがとうございました。

もしよろしければ、以下のまとめ記事もどうぞご覧ください。

◆参考

本記事は、以下の書籍を大いに参考にしています。

より詳しく知りたい方は、どうぞ。

https://www.amazon.co.jp/dp/4797362596

Discussion

◆はじめに

テキストボックスに入力されて、送信されたテキストを

DB送信文字列として画面に返送するアプリケーションです。

そして、「select」「insert」「update」「delete」ボタンで

データベースにデータを送信したり、呼び出したりできます。

(1)「Select」・・・データベースに登録されていた文字を表示

(2)「Insert」・・・文字列をデータベースに登録

(3)「Update」・・・「Updateテキストデータ」という文字列にデータを更新

(4)「Delete」・・・ データベースに登録されているデータを全て削除

◆SQLとは

「Structured Query Language」の略で、直訳すると「構造化問い合わせ言語」

リレーショナルデータベース(RDB)のデータを操作するための言語です。

大きく分けて、「select」「insert」「update」「delete」の四種類があります。

SQL文は、コンピュータ言語のひとつですが、プログラミング言語ではありません。

なので、Eclipseなどで扱う場合は、Stringの型でデータベースへ送ります。

そして、処理はデータベースで行います。

また、データベースにアクセスするためには、

データベースの種類によって異なるライブラリが必要になります。

本記事では、HSQLDBを扱います。

◆前提/環境など

本記事は、以下の記事の「InputFunction」プロジェクトをベースに作成します。

Echo機能のServletの処理時間を計測するFilterを実装しています。

また、Sessionで結果を保持するようになっています。

以下の記事の内容を踏まえた前提で、記事は書いていますのでご了承ください。

仕様は以下です。

<Echo機能> ・文字列を入力し、送信ボタンを押すと、送信した文字列が画面に表示される。

<Validation機能> ・送信された文字列が空欄の場合は,「この項目は必須入力です。」と文字列が表示される。

<TimeFiletr機能(Listener)> ・Servletの処理時間を計測し、コンソールに表示

<Session機能> ・処理結果をSessionがリセットされるまで保持する

※本記事では、 eclipseは以下のバージョンでの画像を使用しています。

日本語化はしていません。

他のversionでの動作は確認しておりません。

◆手順

それでは、実装していきます。

まず、データベースを用意して、次にソースコードを記述していきます。

最後に動作確認をしたら終了です。

①データベース

(1)データベース作成

まず、基本的なところは下記の記事よりお願いします。

その上で、本記事のアプリケーション用に、

DBの構築を行っていきたいと思います。

・ターミナルからDB作成と起動

・下記のディレクトリで、これ以降のコマンドをターミナルに打ちます。

・DB名は「textdb」

※変更したい方は、下記コマンドの該当箇所を変更し、これ以降も読み替えてください。

すると、データベースが起動します。

先程のターミナルは、そのまま開いた状態にしておき、

新規で、別のターミナルを開きます。

こちらから、DBの管理マネージャを開きテーブルを作成します。

アクセス画面で、下記の通り入力します。

※hsqldbの後ろに、「hsql」を書き忘れないように注意しましょう

テーブル名は、「textdata」、パラメータは「text」のみにしました。

・以下のSQL文で、textdataテーブルをつくる

・試しに、Insert文でデータをついかして、

Select文で確認してみましょう

以上で、データベースの作成は完了です。

この後、エクリプスからデータベースに接続するのですが、

こちらのデータベース管理ソフトで接続していると繋がらないので、

ウィンドウを閉じて、接続を切ります。

また反対に、エクリプスで接続している場合は、

管理ソフトで接続できないので、注意しましょう。

(2)Eclipseで接続するためのドライバ

Eclipseと接続する際に、

JAVAの場合は、「JDBC」という仕組みを使用してアクセスします。

そのため、HSQLDB用のJDBCドライバが必要です。

JDBCドライバは、jarファイルでインストールしたHSQLDBのフォルダ内にあります

＜HSQLDBのディレクトリ/lib/hsqldb.jar＞

「hsqldb.jar」があったら、

・Eclipseのプロジェクトのlibフォルダへコピー

・ClassPathを通す

クラスパスの通し方は、ライブラリの場合と一緒ですので以下を参考にして下さい。

(3)Eclipseで接続するための情報

JAVAで接続する際に、

必要なパラメータは以下です。

また、以下の項目をimportします

そして、今回はプロジェクトに、

DBAccessorというサーブレットのクラスを作成し、

その中で、SQL文を接続先のDBへ渡す処理を行っています。

それでは、具体的なコードは次を見てください。

②ソースコード

(4)「Delete」・・・データベースに登録されているデータを全て削除