Advertisement
Guest User

Untitled

a guest
Feb 27th, 2017
64
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.67 KB | None | 0 0
  1. /*
  2. * To change this license header, choose License Headers in Project Properties.
  3. * To change this template file, choose Tools | Templates
  4. * and open the template in the editor.
  5. */
  6. package jdbssqlite;
  7.  
  8. import java.io.FileNotFoundException;
  9. import java.io.FileOutputStream;
  10. import java.io.PrintStream;
  11. import java.sql.Connection;
  12. import java.sql.DatabaseMetaData;
  13. import java.sql.DriverManager;
  14. import java.sql.ResultSet;
  15. import java.sql.SQLException;
  16. import java.util.ArrayList;
  17. import java.util.logging.Level;
  18. import java.util.logging.Logger;
  19.  
  20. /**
  21. *
  22. * @author rikhi
  23. */
  24. public class JDBCSQLiteConnection {
  25.  
  26. static Connection conn = null;
  27. static DatabaseMetaData dm = null;
  28. private ResultSet rs = null;
  29. static private final int STR_SIZE = 25;
  30.  
  31.  
  32.  
  33.  
  34. static String sql = "CREATE ?";
  35.  
  36. static {
  37. try {
  38. conn = DBConnection.getConnection();
  39. } catch (SQLException e) {
  40. System.err.println("There was an error getting the connection: "
  41. + e.getMessage());
  42. } catch (ClassNotFoundException ex) {
  43. Logger.getLogger(JDBCSQLiteConnection. class.getName()).log(Level.SEVERE, null, ex);
  44. }
  45.  
  46. try {
  47. dm = conn.getMetaData();
  48. } catch (SQLException e) {
  49. System.err.println("There was an error getting the metadata: "
  50. + e.getMessage());
  51. }
  52. }
  53.  
  54. public static void printGeneralMetadata() throws SQLException {
  55. System.out.println("JDBC Driver: " + dm.getDriverName());
  56. System.out.println("Driver Version: " + dm.getDriverVersion());
  57. System.out.println("\n");
  58.  
  59. }
  60.  
  61. /* public static ArrayList getTablesMetadata() throws SQLException {
  62. String table[] = { "TABLE" };
  63. ResultSet rs = null;
  64. ArrayList tables = null;
  65. // receive the Type of the object in a String array.
  66. rs = dm.getTables(null, null, null, table);
  67. tables = new ArrayList();
  68. while (rs.next()) {
  69. tables.add(rs.getString("TABLE_NAME"));
  70. }
  71. return tables;
  72. }
  73.  
  74.  
  75. public static void getColumnsMetadata(ArrayList<String> tables)
  76. throws SQLException {
  77. ResultSet rs = null;
  78. // Print the columns properties of the actual table
  79. for (String actualTable : tables) {
  80. rs = dm.getColumns(null, null, actualTable, null);
  81. System.out.println("CREATE TABLE " + actualTable.toLowerCase()+"("+"");
  82. while (rs.next()) {
  83. System.out.println(rs.getString("COLUMN_NAME") + ", "
  84. + rs.getString("TYPE_NAME") + ", "+""
  85. //+ rs.getString("COLUMN_SIZE")+"
  86.  
  87. );
  88.  
  89. }
  90. System.out.println(");");
  91. System.out.println("\n");
  92.  
  93.  
  94.  
  95. }
  96.  
  97. }*/
  98.  
  99. private String pad( String in ) {
  100. byte [] org_bytes = in.getBytes( );
  101. byte [] new_bytes = new byte[STR_SIZE];
  102. int upb = in.length( );
  103.  
  104. if ( upb > STR_SIZE )
  105. upb = STR_SIZE;
  106.  
  107. for ( int i = 0; i < upb; i++ )
  108. new_bytes[i] = org_bytes[i];
  109.  
  110. for ( int i = upb; i < STR_SIZE; i++ )
  111. new_bytes[i] = ' ';
  112.  
  113. return new String( new_bytes ); }
  114.  
  115. public static ResultSet doQuery(String statement)
  116. {
  117. ResultSet rs = null;
  118. try
  119. {
  120. rs = conn.createStatement().executeQuery(statement);
  121. }
  122. catch (Exception ex)
  123. {
  124. ex.printStackTrace();
  125. }
  126. return rs;
  127. }
  128.  
  129. public static void doQuery_1_a( )
  130. {
  131. ArrayList<String> tableNames = new ArrayList<String>();
  132. ArrayList<String>[] columnTypes;
  133. ArrayList<String>[] columnNames;
  134. try
  135. {
  136. DatabaseMetaData dbmd = conn.getMetaData();
  137. ResultSet rs = dbmd.getTables(null, null, "%", null);
  138.  
  139. while (rs.next())
  140.  
  141. tableNames.add(rs.getString(3));
  142. columnTypes = new ArrayList[tableNames.size()];
  143. columnNames = new ArrayList[tableNames.size()];
  144. for (int i = 0; i < tableNames.size(); i++)
  145. {
  146. //ResultSet primaryKeys = dm.getPrimaryKeys(null ,null,tableNames.get(i));
  147. //ResultSet exportedKeys = dm.getExportedKeys(null ,null,tableNames.get(i));
  148. //ResultSet importedKeys = dm.getImportedKeys(null ,null,tableNames.get(i));
  149.  
  150. columnTypes[i] = new ArrayList<String>();
  151. columnNames[i] = new ArrayList<String>();
  152. rs = doQuery( "select * from " + tableNames.get(i));
  153. int numberOfColumns = rs.getMetaData().getColumnCount();
  154. for (int j = 1; j <= numberOfColumns; j++)
  155. {
  156. columnTypes[i].add(rs.getMetaData().getColumnTypeName(j));
  157. columnNames[i].add(rs.getMetaData().getColumnLabel(j));
  158. }
  159.  
  160. while(rs.next())
  161. {
  162. System.out.print("INSERT INTO " + tableNames.get(i)+" VALUES(");
  163. for (int j = 1; j <= numberOfColumns; j++)
  164. {
  165. if (columnTypes[i].get(j-1).toString().equals("VARCHAR"))
  166. System.out.print('\'');
  167. System.out.print(rs.getString(j));
  168. if (columnTypes[i].get(j-1).toString().equals("VARCHAR"))
  169. System.out.print('\'');
  170. if(j==numberOfColumns)
  171. System.out.println(" );");
  172. else
  173. System.out.print(", ");
  174. }
  175.  
  176. }
  177.  
  178.  
  179.  
  180. /*System.out.print("CREATE TABLE " +tableNames.get(i) + " (" );
  181. for (int j = 1; j <= numberOfColumns; j++)
  182. {
  183. System.out.print(columnNames[i].get(j-1) + " "+ columnTypes[i].get(j-1));
  184. if (columnTypes[i].get(j-1).equals("VARCHAR") || columnTypes[i].get(j-1).equals("VARCHAR "))
  185. {
  186. System.out.print("("+rs.getMetaData().getPrecision(j)+")");
  187. }
  188. if(j==numberOfColumns)
  189. {
  190. String primaryKey = ", primary key(";
  191. while (primaryKeys.next())
  192. {
  193. primaryKey += primaryKeys.getString("COLUMN_NAME");
  194. primaryKey += ",";
  195. }
  196. primaryKey = primaryKey.substring(0,primaryKey.length() - 1);
  197.  
  198. primaryKey += ")";
  199. System.out.print(primaryKey);
  200. while(importedKeys.next())
  201. {
  202. System.out.print(",");
  203. System.out.print(" foreign key(" + importedKeys.getString("FKCOLUMN_NAME") + ")");
  204. System.out.print(" references " + importedKeys.getString("PKTABLE_NAME") + "(" +
  205. importedKeys.getString("PKCOLUMN_NAME") + ")");
  206. }
  207. System.out.println(");");
  208. }
  209. else
  210. System.out.print(", ");
  211. }*/
  212.  
  213. }
  214. }
  215. catch (Exception ex)
  216. {ex.printStackTrace();}
  217. }
  218.  
  219.  
  220.  
  221.  
  222.  
  223. public static void main(String[] args){
  224. try{
  225. printGeneralMetadata();
  226. //getColumnsMetadata(getTablesMetadata());
  227. doQuery_1_a( );
  228.  
  229. }catch(SQLException e){
  230. System.err.println("Problem");
  231. }
  232.  
  233.  
  234.  
  235. }
  236.  
  237.  
  238.  
  239.  
  240.  
  241.  
  242. }
  243.  
  244.  
  245.  
  246. /* try {
  247. Class.forName("org.sqlite.JDBC");
  248. String dbURL = "jdbc:sqlite:D:/downloads/University.db";
  249. Connection conn = DriverManager.getConnection(dbURL);
  250. DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
  251.  
  252. if (conn != null) {
  253. System.out.println("Connected to the database");
  254. System.out.println("Driver name: " + dm.getDriverName());
  255. System.out.println("Driver version: " + dm.getDriverVersion());
  256. System.out.println("Product name: " + dm.getDatabaseProductName());
  257. System.out.println("Product version: " + dm.getDatabaseProductVersion());
  258. conn.close();
  259. }
  260. } catch (ClassNotFoundException ex) {
  261. ex.printStackTrace();
  262. } catch (SQLException ex) {
  263. ex.printStackTrace();
  264. }
  265. }*/
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement