Magazine
 

Tips & Tricks

 

workexp table:

empId technology years
emp1 Java 4
emp1 php 2

GetMetadata.java

import java.sql.*;
public class GetMetadata {
public static void main(String[] args) throws
Exception {
Class.forName(“com.mysql.jdbc.Driver”);
Connection con =
DriverManager.getConnection(“jdbc:mysql://
localhost:3306/test”, “root”, “root”);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select
employee.empId, workexp.technology from
employee, workexp where employee.empId =
workexp.empId”);
getColumnNames(rs);
rs.close();
st.close();
con.close();
}
public static void
getColumnNames(ResultSet rs) throws
SQLException {
if (rs == null) {
return;
}
ResultSetMetaData rsmd =
rs.getMetaData();
// Get the number of coulumns
int numberOfColumns =
rsmd.getColumnCount();
for (int i = 1; i < numberOfColumns + 1;
i++) {
System.out.println();
// get the column name at ith index
System.out.println(“Column Name
:”+rsmd.getColumnName(i));

 

 

// get the Data Type of the column
System.out.println(“Data Type
:”+rsmd.getColumnTypeName(i));
// get the Length of the column
System.out.println(“Length
:”+rsmd.getColumnDisplaySize(i));
// Get the table name of the column
System.out.println(“Table Name
:”+rsmd.getTableName(i));
}
}
}

Output of the program:

5. Copy data from Excel to the MySQL

You may want to copy data in excel sheet to the table of MySQL database. This JDBC program can help you to understand how it can be done in java. ExcelToMySQL. Before running this program you have to make dsn.
Open the odbc data source administrator console, create new data source, select microsoft excel driver, give data source name (dsn) and select excel sheet name. In this
example, the dsn name is “myexcel”. Two connections have been created, one for excel and the other for MySQL. Three queries are fired, first one to get the data from excel sheet, the next to create the table of your choice, if doesn’t exist, with all the columns and name
as in excel sheet and the last one to insert the data to the specified table.

Feb 2008 | Java Jazz Up | 66
previous
index
next
 
View All Topics
All Pages of this Issue
Pages: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,

30
, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53 , 54, 55, 56, 57,

58
, 59, 60, 61, 62, 63 , 64, 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 ,

Download PDF