How to pass a Char to the bank?

Question

How to pass a Char to the bank?

Navigation

#1 by (3 votes)
#2 by (2 votes)

0

How do I get a 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;
    }
}

java oracle

asked by anonymous 16.10.2015 / 14:09

2 answers

2

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

I do not recommend it. I also do not recommend using CHAR(2) . It will even settle by coincidence. It is writing 1 character that should occupy 2 bytes in a space of 2 characters. Byte and character are different concepts and to swap them is to ask to have problems. One hour this will not work. Stay with the solution where you write 1 character - which is what you want - that allows multibyte , which is the case of NCHAR .

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

16.10.2015 / 14:21

Buttons made with images Getting the value of the table with ckeckbox and passing to the controller with AngularJS

score 3 · Accepted Answer

The solution is to swap in your PreparedStatement from setInt() to setString() .

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

Using setInt() the variable char will be converted to Integer when sending to the bank. And by converting char to Integer the value sent is the equivalent of ASCII of char , which is why I was being saved 77 , instead of M in the bank.

Here's a table of how JDBC handles 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