CODEWITHSUNDEEP
javamysqljdbc
coderboi27
coderboi27

Reputation: 39

How does JDBC map SQL types in the java.sql.types class that don't exist in MySQL?

In the java.sql.types class, there are types which are easily mapped to MySQL types. For example, VARCHAR in the java.sql.types class is mapped to VARCHAR in MySQL.

However, I am unsure how types in the java.sql.types class such as JAVA_OBJECT can be mapped to types in MySQL when similar types don't exist in MySQL. In such cases, what would the JDBC do?

Upvotes: 4

Views: 1186

Answers (2)

Elyas 'Eloy' Hadizadeh Tasbiti
Elyas 'Eloy' Hadizadeh Tasbiti

Reputation: 3665

As you know, each RDBMS releases its implementation of JDBC, and the JDBC tries to map Java's data-types to the database's. If there were not a suitable mapping option, JDBC would throw java.sql.SQLException: Invalid column type.

I would recommend you to review this link there are useful notes about data-type mapping. According to MySQL's doc, it does not support JAVA_OBJECT, but it seems that IBM Informix and Oracle support it.

Meanwhile, If you want to insert a Java Object to a table, you can map it to BLOB. Suppose you have created a table with the following command:

CREATE TABLE java_objects ( 
  id INT AUTO_INCREMENT, 
  name varchar(128), 
  object_value BLOB, 
  primary key (id));

You can insert your object by using this sample code.

public long insertAnObject(String className, Object javaObject)
{
    int id = -1;
    try
    {
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO java_objects(name, object_value) VALUES (?, ?)");

        // set input parameters
        preparedStatement.setString(1, className);
        preparedStatement.setObject(2, javaObject);
        preparedStatement.executeUpdate();

        // get the generated key for the id
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next())
        {
            id = resultSet.getInt(1);
        }

        resultSet.close();
        preparedStatement.close();
        return id;
    } catch (SQLException e)
    {
        e.printStackTrace();
    }

    return id;
}

Upvotes: 1

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

If JDBC driver does not support a type you try to use - it will try to map it to the nearest possible datatype which is supported. And if it can not do that - SQLException is thrown.

JDBC is just an interface for working with databases. And it's possible that some parts of the API may be not supported by a database. What happens in this case totally depends on driver's implementation. That's common for all JDBC drivers.

Concerning MySQL, you can get the list of supported datatypes and their mappings from the official documentation.

Upvotes: 1

Related Questions