Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to subtract two column of different table using sql query

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 56
    Answer it

    I Have 2 tables WIthdrawMaterial and DepositMaterial,Both In WithdrawMaterial contain quantity column, and DepositMaterial contain totalDeposit ,I want to perform subtraction between quantity and totalDeposit column using query and store the result in pending column of TransactionMaterial table . 

     

    This is my code,  what I tried to solve this issue.

    package com.nilmani.workload.repository
    
    import com.nilmani.workload.entity.TransactionMaterial
    import org.springframework.data.jpa.repository.JpaRepository
    import org.springframework.data.jpa.repository.Modifying
    import org.springframework.data.jpa.repository.Query
    import org.springframework.transaction.annotation.Transactional
    import org.springframework.web.bind.annotation.RequestParam
    
    interface TransactionRepository :JpaRepository<TransactionMaterial,Long> {
    
       @Query("SELECT  WithdrawMaterial .quantity,DepositMaterial .totalDeposit,WithdrawMaterial .quantity-DepositMaterial .totalDeposit  FROM WithdrawMaterial  INNER JOIN DepositMaterial ON WithdrawMaterial .id = DepositMaterial .withDrawId")
        fun pendingMaterial(@RequestParam("withDrawId") withDrawId:Long,@RequestParam("depositId")depositId:Long):Long
    
    }

    But this query Not working properly ,It throws null pointer exception while execute the application 

    My entity class WithdrawMaterial Entity class

    package com.nilmani.workload.entity
    
    import com.nilmani.workload.enum.Material
    import java.time.LocalDateTime
    import javax.persistence.Entity
    import javax.persistence.GeneratedValue
    import javax.persistence.GenerationType
    import javax.persistence.Id
    
    @Entity
    data class WithdrawMaterial(
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        val id:Long=-1,
        val user_id:Long=-1,
        val material:Int = Material.NONE.type,
        val quantity:Long=-1,
        val time:LocalDateTime= LocalDateTime.now(),
    )
    

    DepositMaterial Entity Class

    package com.nilmani.workload.entity
    
    import com.nilmani.workload.enum.Material
    import java.time.LocalDateTime
    import javax.persistence.Entity
    import javax.persistence.GeneratedValue
    import javax.persistence.GenerationType
    import javax.persistence.Id
    
    @Entity
    data class DepositMaterial (
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        val id:Long=-1,
        val withDrawId:Long=-1,
        val totalDeposit:Long=-1,
        val material:Int= Material.NONE.type,
        val depositTime:LocalDateTime = LocalDateTime.now(),
        )

    controllerClass

    @PostMapping("/updateTransaction")
     fun updateWithdrawMaterial(@ModelAttribute request: ReqUpdateWithdrawMaterial):ResponseEntity<*>{
         val existingUser = employeeRepository.findById(request.userId)
         if (existingUser.isPresent){
             val existWork = withdrawMaterialRepository.findById(request.withDrawId)
             return if (existWork.isPresent){
                 val pendingNearUser = TransactionMaterial(
                     userId = request.userId,
                     withDrawId = request.withDrawId,
                     material = request.material,
                     depositId = request.depositId,
                     pending = transactionRepository.pendingMaterial(request.withDrawId,request.depositId)
                 )
                 val saveMaterial = transactionRepository.save(pendingNearUser)
                 return ResponseEntity(saveMaterial,HttpStatus.OK)
             }else{
                 ResponseEntity(ResMessage("you need to learn then we provide work you"),HttpStatus.BAD_REQUEST)
             }
         }else{
             return ResponseEntity(ResMessage("You are not a registered User ! Register First"),HttpStatus.NOT_ACCEPTABLE)
         }
     }

    What is the reason for this type of issue,I cannot find where I got null value ,I provide default value to all the variables ,While using query I got nullPointer Exception ,

    Caused by: java.lang.NullPointerException: null
        at java.base/java.lang.String$CaseInsensitiveComparator.compare(String.java:1244) ~[na:na]
        at java.base/java.lang.String$CaseInsensitiveComparator.compare(String.java:1237) ~[na:na]
        at java.base/java.util.TreeMap.getEntryUsingComparator(TreeMap.java:374) ~[na:na]
        at java.base/java.util.TreeMap.getEntry(TreeMap.java:343) ~[na:na]
        at java.base/java.util.TreeMap.get(TreeMap.java:278) ~[na:na]
        at org.hibernate.dialect.function.SQLFunctionRegistry.findSQLFunction(SQLFunctionRegistry.java:45) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.findSQLFunction(SessionFactoryHelper.java:385) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.tree.IdentNode.getDataType(IdentNode.java:367) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.HqlSqlWalker.lookupProperty(HqlSqlWalker.java:669) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.propertyRef(HqlSqlBaseWalker.java:1159) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2320) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2261) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1523) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:602) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
        at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
        at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.12.jar:5.3.12]
        at com.sun.proxy.$Proxy82.createQuery(Unknown Source) ~[na:na]
        at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90) ~[spring-data-jpa-2.5.6.jar:2.5.6]
        ... 58 common frames omitted
     

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: