JEE – WCD Seminar – Lösungen 2.1 Teil II

Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
JEE – WCD Seminar – Lösungen 2.1
Teil II
Inhaltsverzeichnis
Kapitel 7...........................................................................................................................................2 JDBC............................................................................................................................................2 1. dbzugriff1.jsp .................................................................................................................2 2. DbZugriff1Servlet.java ..................................................................................................3 Data Source ..................................................................................................................................5 3. META-INF/context.xml ................................................................................................5 4. jees-ds.xml .....................................................................................................................6 5. jboss-web.xml ................................................................................................................7 6. dbzugriff2.jsp .................................................................................................................7 7. DbZugriff2Servlet.java ..................................................................................................9 Java Persitence API ...................................................................................................................11 8. src/META-INF/persistence.xml ..................................................................................11 9. DbZugriff3.java ...........................................................................................................13 10. dbzugriff3.jsp ...............................................................................................................14 11. Person.java ...................................................................................................................15 1/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
Kapitel 7
JDBC
1. dbzugriff1.jsp
<%@ page import="java.sql.DriverManager"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.Statement"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="ch.wcds.tools.WebTools"%> <%@ page import="ch.wcds.tools.DbHelper"%> <%@ page import="ch.wcds.tools.HtmlHelper"%> <h1>DB Zugriff 1 -‐ ohne DataSource ...</h1> <% String URL = "jdbc:h2:tcp://localhost/~/test"; String DRIVER = "org.h2.Driver"; String USER = "sa"; String PASSWORD = ""; String DROP = "drop table test1"; String SELECT = "select * from test1"; String CREATE = "create table test1(id int primary key, wert int)"; try { // 1 a) Treiber Registrieren Class.forName(DRIVER); // 1 b) Anwendung mit der Datenbank verbinden Connection conn = DriverManager.getConnection(URL, USER, PASSWORD); // 2) SQL Anweisungsobjekt vorbereiten Statement stm = conn.createStatement(); // 3) SQL Anweisungen ausfuehren try { stm.execute(DROP); } catch (Exception e) { } stm.execute(CREATE); for (int i = 1001; i <= 1015; i++) { stm.execute("insert into test1 values(" + i + "," + i * i + ")"); } ResultSet rs = stm.executeQuery(SELECT); 2/15
Johann Mildner, Basel
%> JEE – WCDS – Lösungen – Teil 2.1
// 4) Resultat der SQL Anweisung (SELECT) verarbeiten while (rs.next()) { int id = rs.getInt(1); int wert = rs.getInt(2); out.println(wert + " " + id + "<br/>"); } // 5) Verbindung mit Datenbank schliessen conn.close(); } catch (Exception e) { out.println("FEHLER AUFGETRETEN <br />" + e); } 2. DbZugriff1Servlet.java
package ch.wcds.servlets; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import ch.wcds.tools.DbHelper; public class DbZugriff1Servlet extends AbstractServlet { private static final long serialVersionUID = 1L; private final String DROP = "drop table test1"; private final String CREATE = "create table test1 (id int primary key, name varchar(100))"; private final String INSERT1 = "insert into test1 values(1,'hugo')"; private final String INSERT2 = "insert into test1 values(2,'max')"; private final String SELECT = "select * from test1"; private final String DRIVER = "org.h2.Driver"; private final String URL = "jdbc:h2:tcp://localhost/~/test"; private final String USER = "sa"; private final String PASSWD = ""; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<h1>DbZugriff1 ...</h1>"); 3/15
Johann Mildner, Basel
} JEE – WCDS – Lösungen – Teil 2.1
try { Class.forName(DRIVER); Connection connection = DriverManager.getConnection(URL, USER, PASSWD); out.println(DbHelper.dbMetadaten(connection)); Statement statement = connection.createStatement(); try { statement.execute(DROP); } catch (Exception e) { // NOOP } statement.execute(CREATE); statement.execute(INSERT1); statement.execute(INSERT2); ResultSet resultSet = statement.executeQuery(SELECT); while (resultSet.next()) { out.println("<br />" + resultSet.getString("id") + " / " + resultSet.getString("name")); } connection.close(); } catch (Exception e) { out.println("FEHLER AUFGETRETEN <br />" + e); } } out.close(); 4/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
Data Source
3. META-INF/context.xml
<?xml version="1.0" encoding="UTF-‐8"?> <Context> <Resource name="jdbc/jeesH2" auth="Container" type="javax.sql.DataSource" driverClassName="org.h2.Driver" url="jdbc:h2:tcp://localhost/~/test" username="sa" password="" /> <Resource name="jdbc/jeesMySql" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" username="root" password="" /> </Context> 5/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
4. jees-ds.xml
<?xml version="1.0" encoding="UTF-‐8"?> <!-‐-‐ ******************************************************************** -‐-‐> <!-‐-‐ jees-‐ds.xml -‐ fuer JBoss -‐-‐> <!-‐-‐ ******************************************************************** -‐-‐> <!-‐-‐ die Datenbanken muessen so wie in <connection-‐url> beschrieben -‐-‐> <!-‐-‐ vorhanden sein -‐-‐> <!-‐-‐ ******************************************************************** -‐-‐> <!-‐-‐ diese Datei (jees-‐ds.xml muss in das Verzeichnis -‐-‐> <!-‐-‐ JBOSS_HOME/server/default/deploy kopiert werden -‐-‐> <!-‐-‐ -‐-‐> <!-‐-‐ die dbDriver.jar Dateien muessen vom JBoss-‐Server gefunden werden -‐-‐> <!-‐-‐ und deshalb in das Verzeichnis JBOSS_HOME/server/default/lib -‐-‐> <!-‐-‐ kopiert werden -‐-‐> <!-‐-‐ ******************************************************************** -‐-‐> <datasources> <local-‐tx-‐datasource> <jndi-‐name>jdbc/jeesH2</jndi-‐name> <connection-‐url>jdbc:h2:tcp://localhost/~/test</connection-‐url> <driver-‐class>org.h2.Driver</driver-‐class> <user-‐name>sa</user-‐name> <password></password> </local-‐tx-‐datasource> <!-‐-‐ <local-‐tx-‐datasource> <jndi-‐name>jdbc/jeesMySql</jndi-‐name> <connection-‐url>jdbc:mysql://localhost:3306/jees</connection-‐url> <driver-‐class>com.mysql.jdbc.Driver</driver-‐class> <user-‐name>jees</user-‐name> <password>jees</password> </local-‐tx-‐datasource> <local-‐tx-‐datasource> <jndi-‐name>jdbc/jeesPostgres</jndi-‐name> <connection-‐url>jdbc:postgresql://localhost:5432/jees</connection-‐url> <driver-‐class>org.postgresql.Driver</driver-‐class> <user-‐name>jees</user-‐name> <password>jees</password> </local-‐tx-‐datasource> <local-‐tx-‐datasource> <jndi-‐name>jdbc/jeesOracle</jndi-‐name> <connection-‐url>jdbc:oracle:thin:@localhost:1521:xe</connection-‐url> <driver-‐class>oracle.jdbc.driver.OracleDriver</driver-‐class> <user-‐name>jees</user-‐name> <password>jees</password> </local-‐tx-‐datasource> -‐-‐> </datasources> 6/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
5. jboss-web.xml
<?xml version="1.0" encoding="UTF-‐8"?> <!DOCTYPE jboss-‐web PUBLIC "-‐//JBoss//DTD Web Application 2.3V2//EN" "http://www.jboss.org/j2ee/dtd/jboss-‐web_3_2.dtd"> <jboss-‐web> <resource-‐ref> <res-‐ref-‐name>jdbc/jeesH2</res-‐ref-‐name> <!-‐-‐ res-‐type>javax.sql.DataSource</res-‐type -‐-‐> <jndi-‐name>java:jdbc/jeesH2</jndi-‐name> </resource-‐ref> </jboss-‐web> 6. dbzugriff2.jsp
<%@ page import="java.sql.Connection"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="java.sql.Statement"%> <%@ page import="java.sql.*"%> <%@ page import="javax.naming.InitialContext"%> <%@ page import="javax.servlet.jsp.JspWriter"%> <%@ page import="javax.sql.DataSource"%> <%@ page import="ch.wcds.tools.DbHelper"%> <%@ page import="ch.wcds.tools.HtmlHelper"%> <h1>DB Zugriff 2 -‐ mit DataSource ...</h1> <% String DROP = "drop table test1"; String SELECT = "select * from test1"; String CREATE = "create table test1(id int primary key, wert int)"; String JNDI_NAME = "java:comp/env/jdbc/jeesH2"; try { // 1 a) DataSource ermitteln InitialContext ctx = new InitialContext(); DataSource ds = (DataSource) ctx.lookup(JNDI_NAME); // 1 b) Anwendung mit der Datenbank verbinden Connection conn; try { conn = ds.getConnection(); } catch (Exception e) { System.out.println("diese Exeption tritt beim ersten getConnection() H2 auf"); conn = ds.getConnection(); System.out.println("ein weiterer getConnection() H2 behebt diesen Fehler"); 7/15
Johann Mildner, Basel
%> JEE – WCDS – Lösungen – Teil 2.1
} // 2) SQL Anweisungsobjekt vorbereiten Statement stm = conn.createStatement(); // 3) SQL Anweisungen ausfuehren try { stm.execute(DROP); } catch (Exception e) { System.out.println(4444); } stm.execute(CREATE); for (int i = 2001; i <= 2015; i++) { stm.execute("insert into test1 values(" + i + "," + i * i + ")"); } ResultSet rs = stm.executeQuery(SELECT); // 4) Resultat der SQL Anweisung (SELECT) verarbeiten while (rs.next()) { int id = rs.getInt(1); int wert = rs.getInt(2); out.println(wert + " " + id + "<br/>"); } // 5) Verbindung mit Datenbank schliessen conn.close(); } catch (Exception e) { out.println("FEHLER AUFGETRETEN <br />" + e); } 8/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
7. DbZugriff2Servlet.java
package ch.wcds.servlets; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import ch.wcds.tools.DbHelper; public class DbZugriff2Servlet extends AbstractServlet { private static final long serialVersionUID = 1L; private final String DROP = "drop table test1"; private final String CREATE = "create table test1 (id int primary key, name varchar(100))"; private final String INSERT1 = "insert into test1 values(1,'hugo')"; private final String INSERT2 = "insert into test1 values(2,'max')"; private final String SELECT = "select * from test1"; private final String JNDI_NAME = "java:comp/env/jdbc/jeesH2"; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<h1>DbZugriff2 ...</h1>"); try { Connection connection = DbHelper.getConnection(JNDI_NAME); out.println(DbHelper.dbMetadaten(connection)); Statement statement = connection.createStatement(); try { statement.execute(DROP); } catch (SQLException e) { // NOOP } statement.execute(CREATE); statement.execute(INSERT1); statement.execute(INSERT2); 9/15
Johann Mildner, Basel
} JEE – WCDS – Lösungen – Teil 2.1
ResultSet resultSet = statement.executeQuery(SELECT); while (resultSet.next()) { out.println("<br />" + resultSet.getString("id") + " / " + resultSet.getString("name")); } connection.close(); } catch (Exception e) { out.println("FEHLER AUFGETRETEN <br />" + e); } } out.close(); 10/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
Java Persitence API
8. src/META-INF/persistence.xml
<?xml version="1.0" encoding="UTF-‐8" ?> <!-‐-‐ persistence.xml WCDS JPA 2.0 ECLIPSELINK JTA=RESOURCE_LOCAL -‐-‐> <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-‐instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"> <!-‐-‐ persistence-‐unit name=|MYSQL|H2|ORACLE|POSTGRES| -‐-‐> <!-‐-‐ eclipselink.ddl-‐generation=|none|create-‐tables|drop-‐and-‐create-‐tables| -‐-‐> <!-‐-‐ eclipselink.logging.level= |OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST|ALL| -‐-‐> <!-‐-‐ eclipselink.ddl-‐generation.output-‐mode=|sql-‐script|database|both| -‐-‐> <persistence-‐unit name="H2" transaction-‐type="RESOURCE_LOCAL"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <exclude-‐unlisted-‐classes>false</exclude-‐unlisted-‐classes> <properties> <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/~/test" /> <property name="javax.persistence.jdbc.user" value="sa" /> <property name="javax.persistence.jdbc.password" value="" /> <property name="eclipselink.ddl-‐generation" value="drop-‐and-‐create-‐tables" /> <property name="eclipselink.logging.level" value="finer" /> <property name="eclipselink.ddl-‐generation.output-‐mode" value="both" /> </properties> </persistence-‐unit> 11/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
<persistence-‐unit name="MYSQL" transaction-‐type="RESOURCE_LOCAL"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <exclude-‐unlisted-‐classes>false</exclude-‐unlisted-‐classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="" /> <property name="eclipselink.ddl-‐generation" value="drop-‐and-‐create-‐tables" /> <property name="eclipselink.logging.level" value="finer" /> <property name="eclipselink.ddl-‐generation.output-‐mode" value="both" /> </properties> </persistence-‐unit> </persistence> 12/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
9. DbZugriff3.java
package ch.wcds.servlets; import java.io.IOException; import java.io.PrintWriter; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import ch.wcds.entities.Person; public class DbZugriff3Servlet extends HttpServlet { private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<h1>DbZugriff3 ...</h1>"); EntityManagerFactory emf = Persistence.createEntityManagerFactory("H2"); EntityManager em = emf.createEntityManager(); Person p = new Person("servlet"); em.getTransaction().begin(); em.persist(p); em.getTransaction().commit(); em.close(); out.println(p); out.close(); } } 13/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
10. dbzugriff3.jsp
<%@ page import="java.sql.*"%> <%@ page import="javax.naming.*"%> <%@ page import="javax.servlet.jsp.*"%> <%@ page import="javax.persistence.*"%> <%@ page import="ch.wcds.entities.*"%> <h1>DB Zugriff 3 -‐ mit JPA ...</h1> <% EntityManagerFactory emf = Persistence.createEntityManagerFactory("H2"); EntityManager em = emf.createEntityManager(); Person p = new Person("jsp"); em.getTransaction().begin(); em.persist(p); em.getTransaction().commit(); em.close(); out.println(p); %> 14/15
Johann Mildner, Basel
JEE – WCDS – Lösungen – Teil 2.1
11. Person.java
package ch.wcds.entities; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; @Entity public class Person { @Id @GeneratedValue private int id; private String name; public Person() { } public Person(String name) { this.name = name; } public int getId() { return id; } public String getName() { return name; } public void setId(int id) { this.id = id; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + "]"; } public void show() { System.out.println(this); } } 15/15

JEE – WCD Seminar – Lösungen 2.1 Teil II

Produkte

Unterstützung

JEE – WCD Seminar – Lösungen 2.1 Teil II

Dieses Dokument Sammlung (en)

Dieses Dokument gespeichert

Schlagen Sie uns vor, wie wir StudyLib verbessern können