Like passing a Char to the bank?

Question

How do I pass one Char to the Bank?

Error

Exception in thread "main" java.sql.SQLException: ORA-12899: valor muito grande para a coluna "SYSTEM"."USUARIO"."TP_SEXO" (real: 2, máximo: 1)

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at br.com.younews.dao.UsuarioDao.adiciona(UsuarioDao.java:33)
    at br.com.younews.teste.TesteUsuarioDao.main(TesteUsuarioDao.java:20)

my code:

package br.com.younews.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.younews.beans.Usuario;
import br.com.younews.conexao.ConexaoFactory;

public class UsuarioDao {
    private Connection conn;
    private List<Usuario> user = new ArrayList<Usuario>();

    public UsuarioDao() throws Exception {
        this.conn = new ConexaoFactory().getConnection();
    }

    public Usuario adiciona(Usuario usuario) throws SQLException{
        String sql = "INSERT INTO USUARIO (EMAIL_USUARIO, NM_USUARIO, SENHA_USUARIO, TP_SEXO, DT_NASCIMENTO, FOTO_USUARIO, NM_LOCALIZACAO, NM_SOBRENOME, NM_NOME) VALUES (?,?,?,?,?,?,?,?,?)";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, usuario.getEmail());
        st.setString(2, usuario.getNmUsuario());
        st.setString(3, usuario.getSenha());
        st.setInt(4, usuario.getSexo());
        st.setString(5, usuario.getDataNasc());
        st.setString(6, usuario.getFotoPerfil());
        st.setString(7, usuario.getLocalizacao());
        st.setString(8, usuario.getNome());
        st.setString(9, usuario.getSobrenome());
        st.execute();
        st.close();
        return usuario;
    }

    public List<Usuario> listarUsuario() throws Exception{
        List<Usuario> user = new ArrayList<Usuario>();
        PreparedStatement p = conn.prepareStatement("SELECT P.CD_USUARIO FROM USUARIO P LEFT JOIN USUARIO_AMIGO E ON E.USUARIO_CD_USUARIO1 = E.CD_AMIGO");
        ResultSet rs = p.executeQuery();
        while(rs.next()){
            Usuario usuario = new Usuario();
            usuario.setIdLogin(rs.getString("CD_USUARIO"));
            usuario.setNmUsuario(rs.getString("NM_USUARIO"));
        }
        rs.close();
        p.close();
        return user;
    }
}

Answer 1

The real solution is to use a NCHAR in the database. This type is suitable for storing Unicode characters, as is the case with the one used in Java. If you choose not to do this you will have to make a conversion with possible loss of value.

I do not recommend it. I also do not recommend using CHAR(2). It will even solve by coincidence. It is recording 1 character that should occupy 2 bytes in a space for 2 characters. Byte and character are different concepts and to exchange them is to ask for trouble. One hour this will not work. Stick to the solution where you save 1 characters - which is what you want - which allows you to be multibyte, which is the case with NCHAR.

By the comment below I think the problem wasn’t even Unicode. Probably if using a setString() in place of a setInt() will solve the problem without changing the type in the database. It seems to me that you are writing ASCII even. Only the use of the wrong method is that it was forcing to have a larger text.

Answer 2

The solution is to trade in your PreparedStatement of setInt() for setString().

st.setString(4, String.valueOf(usuario.getSexo()));

Using the setInt() the variable char will be converted to Integer when sending to the bank. And when converting a char for Integer the value sent is the equivalent of the code ASCII of char, that’s why I was being saved 77, instead of M at the bank.

Here’s a table of how JDBC treats Java types:

Tipo JDBC              Tipo Java
-------------------------------------------
CHAR                   String
VARCHAR                String
LONGVARCHAR            String
NUMERIC                java.math.BigDecimal
DECIMAL                java.math.BigDecimal
BIT                    boolean
BOOLEAN                boolean
TINYINT                byte
SMALLINT               short