Remote Database connection MYSQL

[Former Member]

Hi All,

We are working on CE 7.1.

We have a requirement of getting list of employee list from MYSQL database.we have done the fallowing steps:

1) Deployed MySQL compatable driver through java tenet

2) Deployed datasource(say mydatasource) successfully.

3) I tested this data source with TEST_DS command and it was fine.

Now i want to develop webservice which returns the list of employees from the MYSQL database.

Can you plz send me the sample code of accessing this datasource and getting connection to that in a java class .

Thanks

Sampath.G

[Former Member · Accepted solution]

Hi,

Following is the code

Connection conn = null;
try {
			InitialContext initialContext = new InitialContext();
//Change the alias name to that of yours.
			DataSource dataSource = (DataSource)initialContext.lookup("jdbc/Alias_Maxdb");
			connection= dataSource.getConnection();
//Here the table name is table one which contains 1 field of data type string, change the following as per yours.
			PreparedStatement stmt = conn.prepareStatement("insert into table1 values(?)"); 
			
			//get the value entered in the inputfield which is mapped to an attribute called ID, change ths according to your req.
			String value = "abc123";
			conn.setAutoCommit(false);
			stmt.setString(1, value);
			stmt.execute();
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

Regards

Ayyapparaj