How to connect to Mysql table in java?
I am hosting a spring application on JBoss7.1AS standalone on my dev box and mysql on OpenShift.
Using port forwarding, i am able to connect to mysql.
The issue is when i am trying to connect to mysql in through a java class.
I don't want to use any beans! There is not point for me to use it, if my server connects to mysql db fine.
How do i correctly call the mysql connection in my java file, which MYSQL dv connected in the standalone.xml?
Here is everything
STANDALONE.XML on my JBoss 7.1 AS
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<datasource jndi-name="java:jboss/datasources/MysqlDS" pool-name="MysqlDS" enabled="true" use-java-context="true">
<connection-url>jdbc:mysql://127.0.0.1:3307/spring</connection-url>
<driver>mysql</driver>
<security>
<user-name>admin</user-name>
<password>XXXXXX</password>
</security>
</datasource>
<drivers>
<driver name="h2" module="com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
<driver name="mysql" module="com.mysql"/>
</drivers>
</datasources>
PERSISTANCE.XML
<persistence version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="primary">
<!-- If you are running in a production environment, add a managed
data source, this example data source is just for development and testing! -->
<!-- The datasource is deployed as WEB-INF/spring-quickstart-ds.xml, you
can find it in the source at src/main/webapp/WEB-INF/spring-quickstart-ds.xml -->
<jta-data-source>java:jboss/datasources/MysqlDS</jta-data-source>
<properties>
<property name="jboss.entity.manager.factory.jndi.name" value="java:jboss/spring-quickstart/persistence" />
<!-- Properties for Hibernate -->
<property name="hibernate.hbm2ddl.auto" value="create-drop" />
<property name="hibernate.show_sql" value="false" />
</properties>
</persistence-unit>
</persistence>
SPRING-DS.XML (I don't know why i have to put it in here?, is this nessary? my server is already conencted to the mysql)
<?xml version="1.0" encoding="UTF-8"?>
<datasources xmlns="http://www.jboss.org/ironjacamar/schema">
<datasource jndi-name="java:jboss/datasources/MysqlDB" pool-name="MysqlDS">
<connection-url>jdbc:mysql://localhost:3307/spring</connection-url>
<driver>mysql</driver>
</datasource>
</datasources>
Memberclass.java
package pagehelpers;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import pagemodels.MemberModel;
public class MemberSignupHelper {
public interface Members{
public void insert(MemberModel member);
}
@Resource(mappedName = "java:jboss/datasources/MysqlDS")
private DataSource dataSource;
public void set(DataSource dataSource) {
this.dataSource = dataSource;
}
public void insert(MemberModel member) {
String sql = "INSERT INTO CUSTOMER (name, email) VALUES (?,?)";
Connection conn = null;
try {
conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, member.getName());
ps.setString(2, member.getEmail());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {}
}
}
}
}
ERROR: NullpointerException because it won't get connection to my mysql database.
What am i doing wrong?
