JDBC
Table metadata sample Servlet
Tomcat 7 conf/context.xml datasource example
1 package org.allowed.bitarus;
2
3 import javax.servlet.*;
4 import javax.servlet.http.*;
5 import java.io.*;
6
7 import java.security.Security;
8 import java.util.Properties;
9 import javax.naming.Context;
10 import javax.naming.InitialContext;
11 import org.allowed.bitarus.IWSTest;
12
13 import javax.annotation.Resource;
14 import javax.sql.DataSource;
15 import java.sql.Connection;
16 import java.sql.PreparedStatement;
17 import java.sql.ResultSet;
18 import java.sql.DatabaseMetaData;
19 import java.sql.SQLException;
20
21 // http://localhost:8081/WebApp/servletxxx
22 public class Servletx extends HttpServlet{
23 @Resource(name="jdbc/mysqlx")
24 private DataSource ds;
25
26 public void service(HttpServletRequest req, HttpServletResponse res) throws IOException
27 {
28 res.setContentType("text/html");
29 PrintWriter out = res.getWriter();
30 String text="";
31 try{
32 Properties env = new Properties();
33 env.put(Context.INITIAL_CONTEXT_FACTORY, "org.jnp.interfaces.NamingContextFactory");
34 env.put(Context.URL_PKG_PREFIXES, "org.jboss.naming:org.jnp.interfaces");
35 env.put(Context.PROVIDER_URL, "jnp://127.0.0.1:1099");
36 InitialContext context = new InitialContext(env);
37 IWSTest test = (IWSTest) context.lookup("WSTest/remote");
38 text=test.helloWorld();
39 }
40 catch(Exception ex){
41 }
42 out.println("<html><head><title>Hello World!</title></head>");
43 out.println(String.format("<body><h1>Hello World!!! %s </h1>",text));
44
45 try{
46 Connection conn = ds.getConnection();
47 PreparedStatement ps = conn.prepareStatement("select * from ost_ticket;");
48 ResultSet rs = ps.executeQuery();
49 while(rs.next()){
50 out.println("<p>"+ rs.getString("name") +"</p>");
51 }
52 rs.close();
53 conn.close();
54 }catch(Exception ex){
55 System.out.println(ex.getMessage());
56 }
57
58 getTableInfo("",out);
59 out.println("</body></html>");
60 }
61
62 private String getDataType(int dt,int nullable){
63 if(dt==93 ) return "Date";
64 if(dt==12 ) return "String";
65 if(dt==-7 && nullable==0) return "boolean";
66 if(dt==-1) return "String";
67 if(dt==4 && nullable==0) return "int";
68 if(dt==1 && nullable==0) return "int";
69 if(dt==-6 && nullable==0) return "int";
70
71 if(dt==-7 && nullable==1) return "Boolean";
72 if(dt==4 && nullable==1) return "Integer";
73 if(dt==1 && nullable==1) return "Integer";
74 if(dt==-6 && nullable==1) return "Integer";
75
76 return "";
77 }
78 private void showColumns(DatabaseMetaData dmd,String table,PrintWriter out) throws SQLException {
79 ResultSet rs = dmd.getColumns(null,null,table,null);
80 while(rs.next()){
81 String column = rs.getString("COLUMN_NAME");
82 int dataType = rs.getInt("DATA_TYPE");
83 int nullable = rs.getInt("NULLABLE");
84 out.println("<p> private "+ getDataType(dataType,nullable) + " " + column + "; // " +nullable +"</p>");
85 }
86 out.println("<p> } </p>");
87 }
88 private void getTableInfo(String tableName,PrintWriter out){
89 try{
90 Connection conn = ds.getConnection();
91 DatabaseMetaData metaData = conn.getMetaData();
92 String[]types={"TABLE"};
93 ResultSet rs = metaData.getTables(null,null,null,types);
94
95 while(rs.next()){
96 String table = rs.getString("TABLE_NAME");
97 out.println("<p> public class "+ table +" { </p>");
98 showColumns(metaData,table,out);
99 }
100 rs.close();
101 conn.close();
102 }catch(Exception ex){
103 System.out.println(ex.getMessage());
104 }
105 }
106 }
MySQL connector maven dependency
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
MySQL get JDBC connection
Oracle thin client JDBC connection