CODEWITHSUNDEEP
javaspring-bootjdbctemplate
Gloria Santin
Gloria Santin

Reputation: 2136

Insert row using jdbctemplate with PrimaryKey generated by @GeneratedValue

I am inserting a row into an Oracle table in a Spring Boot application. The Primary Key needs to be generated using annotations. I have an entity model that represents the table:

@Entity
@Table(name="PURCH_TENDR")
public class LhlPurchTendrModel implements Serializable {

    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name="PURCH_TENDR_ID")
    private String purchTendrId;

    @Column(name="CREATED_BY_NM")
    private String createdByNm;

    @Column(name="CREATED_DT")
    private Timestamp createdDt;

    @Column(name="UPDATED_BY_NM")
    private String updatedByNm;

    @Column(name="UPDATED_DT")
    private Timestamp updatedDt;

    @Column(name="MODIFY_BY_NM")
    private String modifyByNm;

    @Column(name="MODIFY_DT")
    private Timestamp modifyDt;

    @Column(name="CARRIER_TENDER_ID")
    private long CarrierTenderId;

    @Column(name="EVENT_GMT_TM")
    private Timestamp EventGmtTm;

    @Column(name="PURCH_COST_ID")
    private int PurchCostId;

    @Column(name="LAT")
    private float Lat;

    @Column(name="LON")
    private float Lon;

    @Column(name="CITY_NM")
    private String cityNm;

    @Column(name="STATE_CD")
    private String stateCd;

    @Column(name="CARER_EDI_NBR")
    private String carerEdiNbr;

    @Column(name="EVENT_STAT_CD")
    private String eventStatCd;

    @Column(name="ETN_TM")
    private Timestamp EtnTm;

    @Column(name="PCKUP_NBR")
    private String PickupNbr;

    @Column(name="VIN")
    private String Vin;

    @Column(name="EQUIP_NBR")
    private String EquipNbr;

    @Column(name="EQUIP_PREFIX")
    private String EquipPrefix;

There are also getters and setters for these member variables. I use a Repository class to implements a jdbctemplate to insert the row. When I use this variation of the insert, I get the error that the column type is invalid:

public boolean insertPurchaseInfo(LhlPurchTendrModel lhlPurchTendrModel) throws SQLException {
        boolean success= false;
        String ds = lhlJdbcTemplate.getDataSource().getConnection().getSchema();
        LOGGER.info("Schema and Insert Purchase Info {}", ds);
        String insertSequenceNbrSQLStatement = "INSERT INTO purch_tendr(created_by_nm, created_dt, modify_by_nm, modify_dt, carrier_tender_id, purch_cost_id, event_stat_cd, equip_nbr, equip_prefix) " +
                "VALUES (?, SYSDATE, ?, SYSDATE, ?, ?, ?, ?, ?)";
        try{
            int rowsInserted = lhlJdbcTemplate.update(
                    insertSequenceNbrSQLStatement,                
                    new Object[] {lhlPurchTendrModel});

            if(rowsInserted > 0){
                success = true;
            }
        }

When I try to insert using this code, I get the error 'cannot insert NULL into table Purch_Tendr column Purch_Tendr_Id.

public boolean insertPurchaseInfo(LhlPurchTendrModel lhlPurchTendrModel) throws SQLException {
        boolean success= false;
        String ds = lhlJdbcTemplate.getDataSource().getConnection().getSchema();
        LOGGER.info("Schema and Insert Purchase Info {}", ds);
        String insertSequenceNbrSQLStatement = "INSERT INTO purch_tendr(created_by_nm, created_dt, modify_by_nm, modify_dt, carrier_tender_id, purch_cost_id, event_stat_cd, equip_nbr, equip_prefix) " +
                "VALUES (?, SYSDATE, ?, SYSDATE, ?, ?, ?, ?, ?)";
        try{
            int rowsInserted = lhlJdbcTemplate.update(
                    insertSequenceNbrSQLStatement,                
                      new Object[]{lhlPurchTendrModel.getCreatedByNm(), lhlPurchTendrModel.getModifyByNm(), lhlPurchTendrModel.getCarrierTenderId(), lhlPurchTendrModel.getPurchCostId(),
                                lhlPurchTendrModel.getEventGmtTm(), lhlPurchTendrModel.getEquipNbr(), lhlPurchTendrModel.getEquipPrefix()});
            if(rowsInserted > 0){
                success = true;
            }
        }

I am not sure how to use the @Entity class with JdbcTemplate. How do I indicate to JdbcTemplate to generate the primary key value?

Upvotes: 0

Views: 1353

Answers (1)

Ken Chan
Ken Chan

Reputation: 90457

You can't because @Entity and all annotations you use such as @GeneratedValue, @GenericGenerator etc come from JPA while JdbcTemplate behind scene is based on JDBC only which does not know anything about JPA.

If you want to use JPA to manage your data , what you need to look is to choose a JPA implementation (e.g Hibernate is a popular one) and study how to use it through JPA interface but not looking at JdbcTemplate.

Once you get the basic ideas to manage data using JPA , you may consider to look at spring data which is a more high level tool build on top of pure JPA that can help to implement repository / DAO kind of stuff for managing and querying the data.

Upvotes: 1

Related Questions