JEE - JDBC Archives | Big Data & Java Success

Blog Archives

02: JDBC with MySQL, Datasource, and connection pool Tutorial

Step 1: pom.xml file with commons-dbcp2 for the connection pooling and abstraction layer for the datasource.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.mytutorial</groupId>
    <artifactId>simple-persistence</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>simple-persistence</name>
    <url>http://maven.apache.org</url>

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>


        <!-- MySQL Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

       <!-- Connection Pool -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

<groupId>com.mytutorial</groupId>

<artifactId>simple-persistence</artifactId>

<version>1.0-SNAPSHOT</version>

<name>simple-persistence</name>

<url>http://maven.apache.org</url>

<maven.compiler.source>1.8</maven.compiler.source>

<maven.compiler.target>1.8</maven.compiler.target>

<java.version>1.8</java.version>

</properties>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-dbcp2</artifactId>

</dependency>

</dependencies>

<build>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-shade-plugin</artifactId>

<phase>package</phase>

<goals>

<goal>shade</goal>

</goals>

</execution>

</executions>

</plugin>

</plugins>

</build>

</project>

db.properties for connection details

Step 2: Create a new source folder “src/main/resources”.

Step 3: Define the MySQL database “learnjavadb” connection properties in a new file named “db.properties”

…

12 FAQ JDBC interview Q&As

Q1. What are JDBC Statements? What are different types of statements? How can you create them? A1. A statement object is responsible for sending the SQL statements to the Database. Statement objects are created from the connection object and then executed.

Statement stmt = myConnection.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT id, name FROM myTable where id =1245”); //to read
                            or
stmt.executeUpdate(“INSERT INTO (field1,field2) values (1,3)”); //to insert

Statement stmt = myConnection.createStatement();

ResultSet rs = stmt.executeQuery(“SELECT id, name FROM myTable where id =1245”); //to read

stmt.executeUpdate(“INSERT INTO (field1,field2) values (1,3)”); //to insert

The types of statements are: Statement (regular statement...

This content is for 100-Day-Full-Access, 200-Day-Full-Access, 365-Day-Full-Access, and 2-Year-Full-Access members only. Register 50+ Free Java FAQs 50+ Free Big Data FAQs

Already a member? Log in here

JDBC NamedParameterStatement versus PreparedStatement

Q. When will you use NamedParameterStatement over PreparedStatement?
A.

Reason 1: PreparedStatement uses anonymous parameters that are accessed by index, which is ok when you have 2 or 3 parameters,

…

JDBC Overview Interview Questions and Answers

Q01. What is JDBC? How do you connect to a database?
A01. JDBC stands for Java DataBase Connectivity. It is an API which provides easy connection to a wide range of databases.

…

Spring, JavaConfig (i.e @Configuration) & JDBC by example

Example #1 Wiring JDBC Datasource


@Configuration
@ImportResource("application.yml")
public class AppConfig
{
    @Autowired
    private Environment environment;

    @Bean
    public DriverManagerDataSource datasource() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driver-class-name"));
        ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
        ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
        ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
        return ds;
    }
}

@Configuration

@ImportResource("application.yml")

public class AppConfig

{

@Autowired

private Environment environment;

@Bean

public DriverManagerDataSource datasource() {

DriverManagerDataSource ds = new DriverManagerDataSource();

ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driver-class-name"));

ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));

ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));

ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));

return ds;

}

application.yml where the datasource properties are read.


############
# Datasource properties
############
spring:
  datasource:
    driver-class-name: com.sybase.jdbc4.jdbc.SybDataSource
    url: jdbc:sybase:Tds:mydbhost:5700/my_db
    username: user
    password: pwd

############

# Datasource properties

############

spring:

datasource:

driver-class-name: com.sybase.jdbc4.jdbc.SybDataSource

url: jdbc:sybase:Tds:mydbhost:5700/my_db

username: user

password: pwd

Example #2 Wiring JDBC Datasource with JNDI


@Configuration
@ImportResource("application.yml")
public class AppConfig
{
    @Autowired
    private Environment environment;

    @Bean
    public DataSource dataSource() {
        final JndiDataSourceLookup jndiLookup = new JndiDataSourceLookup();
        final String dataSourceJndi = environment.getRequiredProperty("spring.datasource.jndi");
        return jndiLookup.getDataSource(dataSourceJndi);
    }
}

@Configuration

@ImportResource("application.yml")

public class AppConfig

{

@Autowired

private Environment environment;

@Bean

public DataSource dataSource() {

final JndiDataSourceLookup jndiLookup = new JndiDataSourceLookup();

final String dataSourceJndi = environment.getRequiredProperty("spring.datasource.jndi");

return jndiLookup.getDataSource(dataSourceJndi);

}

Example #3 Wiring JDBC Embedded Datasource for integration testing


@Configuration
@ImportResource("application.yml")
public class AppConfig
{
    @Autowired
    private Environment environment;

    @Bean
    public DataSource dataSource() {
        final EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        builder.setType(EmbeddedDatabaseType.HSQL);
        builder.addScript("classpath:hsqldb-oracle.sql");
        builder.addDefaultScripts(); // defaults are schema.sql and data.sql
        return builder.build();
    }
}

@Configuration

@ImportResource("application.yml")

public class AppConfig

{

@Autowired

private Environment environment;

@Bean

public DataSource dataSource() {

final EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();

builder.setType(EmbeddedDatabaseType.HSQL);

builder.addScript("classpath:hsqldb-oracle.sql");

builder.addDefaultScripts(); // defaults are schema.sql and data.sql

return builder.build();

}

Example #4 Wiring up the JdbcTemplate by passing in the datasource


@Configuration
@ImportResource("application.yml")
public class AppConfig
{
    @Autowired
    private Environment environment;
	
	@Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(datasource());
    }

    @Bean
    public DataSource dataSource() {
       //......
    }
}

@Configuration

@ImportResource("application.yml")

public class AppConfig

{

@Autowired

private Environment environment;

@Bean

public JdbcTemplate jdbcTemplate() {

return new JdbcTemplate(datasource());

}

@Bean

public DataSource dataSource() {

//......

}

Example #5 Wiring up a DAO...

This content is for 100-Day-Full-Access, 200-Day-Full-Access, 365-Day-Full-Access, and 2-Year-Full-Access members only. Register 50+ Free Java FAQs 50+ Free Big Data FAQs

Already a member? Log in here

Categories

Blog Archives

02: JDBC with MySQL, Datasource, and connection pool Tutorial

12 FAQ JDBC interview Q&As

JDBC NamedParameterStatement versus PreparedStatement

JDBC Overview Interview Questions and Answers

Spring, JavaConfig (i.e @Configuration) & JDBC by example

300+ Java Interview FAQs

300 + Big Data Interview FAQs

16+ Java Tech Key Areas

10+ Companion Techs Q&As

300+ Java Interview Q&As

Tutorials on Java & Big Data

50+ Free Java & Big Data Interview Q&As

Disclaimer