= JDBC =
== Table metadata sample Servlet ==
Tomcat 7 conf/context.xml datasource example
{{{#!highlight xml
<Context>
<Resource
name="jdbc/mysqlx"
auth="Container"
type="javax.sql.DataSource"
maxActive="50"
maxIdle="30"
maxWait="10000"
username="root"
password="********"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/osticket"/>
</Context>
}}}
{{{#!highlight java
package org.allowed.bitarus;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.security.Security;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import org.allowed.bitarus.IWSTest;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
// http://localhost:8081/WebApp/servletxxx
public class Servletx extends HttpServlet{
@Resource(name="jdbc/mysqlx")
private DataSource ds;
public void service(HttpServletRequest req, HttpServletResponse res) throws IOException
{
res.setContentType("text/html");
PrintWriter out = res.getWriter();
String text="";
try{
Properties env = new Properties();
env.put(Context.INITIAL_CONTEXT_FACTORY, "org.jnp.interfaces.NamingContextFactory");
env.put(Context.URL_PKG_PREFIXES, "org.jboss.naming:org.jnp.interfaces");
env.put(Context.PROVIDER_URL, "jnp://127.0.0.1:1099");
InitialContext context = new InitialContext(env);
IWSTest test = (IWSTest) context.lookup("WSTest/remote");
text=test.helloWorld();
}
catch(Exception ex){
}
out.println("<html><head><title>Hello World!</title></head>");
out.println(String.format("<body><h1>Hello World!!! %s </h1>",text));
try{
Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement("select * from ost_ticket;");
ResultSet rs = ps.executeQuery();
while(rs.next()){
out.println("<p>"+ rs.getString("name") +"</p>");
}
rs.close();
conn.close();
}catch(Exception ex){
System.out.println(ex.getMessage());
}
getTableInfo("",out);
out.println("</body></html>");
}
private String getDataType(int dt,int nullable){
if(dt==93 ) return "Date";
if(dt==12 ) return "String";
if(dt==-7 && nullable==0) return "boolean";
if(dt==-1) return "String";
if(dt==4 && nullable==0) return "int";
if(dt==1 && nullable==0) return "int";
if(dt==-6 && nullable==0) return "int";
if(dt==-7 && nullable==1) return "Boolean";
if(dt==4 && nullable==1) return "Integer";
if(dt==1 && nullable==1) return "Integer";
if(dt==-6 && nullable==1) return "Integer";
return "";
}
private void showColumns(DatabaseMetaData dmd,String table,PrintWriter out) throws SQLException {
ResultSet rs = dmd.getColumns(null,null,table,null);
while(rs.next()){
String column = rs.getString("COLUMN_NAME");
int dataType = rs.getInt("DATA_TYPE");
int nullable = rs.getInt("NULLABLE");
out.println("<p> private "+ getDataType(dataType,nullable) + " " + column + "; // " +nullable +"</p>");
}
out.println("<p> } </p>");
}
private void getTableInfo(String tableName,PrintWriter out){
try{
Connection conn = ds.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
String[]types={"TABLE"};
ResultSet rs = metaData.getTables(null,null,null,types);
while(rs.next()){
String table = rs.getString("TABLE_NAME");
out.println("<p> public class "+ table +" { </p>");
showColumns(metaData,table,out);
}
rs.close();
conn.close();
}catch(Exception ex){
System.out.println(ex.getMessage());
}
}
}
}}}
== MySQL connector maven dependency ==
{{{
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
}}}
== MySQL get JDBC connection ==
{{{#!highlight java
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "userx","passx");
} catch (Exception e) {
System.out.println(e.getMessage());
}
}}}
== Oracle thin client JDBC connection ==
{{{#!highlight java
Connection conn=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "userx","passx");
} catch (Exception e) {
System.out.println(e.getMessage());
}
}}}
== jdbc code gen project ==
* Check https://code.google.com/p/jdbc-code-gen/