could you review my code? package com.toy.world.dao;
import com.toy.world.User;
import java.sql.*;
public class UserDao {
public void add(User user) throws ClassNotFoundException, SQLException {
Class.forName("응.database.windows.net");
Connection c = DriverManager.getConnection(
"jdbc:sqlserver://localhost/springbook", "spring", "book");
PreparedStatement ps = c.prepareStatement(
"insert into user(id, name, password) values(?,?,?)");
ps.setString(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getPassword());
ps.executeUpdate();
ps.close();
c.close();
}
Save & Submit
Cancel
Based on the code you provided, it appears to be responsible for adding a new user to a SQL Server database. Here are a few comments on the code:
ClassNotFoundException
The ClassNotFoundException in the add method suggests that you're attempting to load a JDBC driver class for your SQL Server database. However, the class name provided seems to be incorrect - it should be the fully qualified name of the JDBC driver class. For example, if you're using the Microsoft SQL Server JDBC Driver, the class name would be com.microsoft.sqlserver.jdbc.SQLServerDriver.
SQL injection vulnerability
The PreparedStatement used to execute the insert statement is a good way to prevent SQL injection attacks. However, you're not validating the user input for the three parameters passed to the statement. If any of these values contain malicious SQL code, they will be executed against your database, potentially leading to data loss or theft. It's important to sanitize user input before passing it to a database statement.
Connection management
It's generally a good practice to use connection pooling to manage database connections, rather than creating a new connection for each operation. This can improve performance and reduce the overhead of creating and tearing down database connections. You might want to look into using a connection pool library, such as HikariCP or Apache DBCP.
Resource cleanup
It's important to ensure that database resources are properly cleaned up after use, regardless of whether an exception is thrown or not. In this case, the PreparedStatement and Connection objects should be closed in a finally block to ensure they're always released, even if an exception occurs.
Overall, the code appears to be functional for adding a new user to a SQL Server database, but there are a few areas where it could be improved for performance, security, and resource management.
Kim
"응.database.windows.net" is my server name
I see, thank you for clarifying. In that case, the Class.forName() method call is not necessary, as it's typically used to load the JDBC driver class for the specific database being used. Instead, you should use the DriverManager.getConnection() method to establish a connection to your SQL Server database, like you're already doing in the code.
So the corrected code
Kim
i modified like this "public class UserDao {
public void add(User user) throws SQLException {
//Class.forName("응.database.windows.net");
try {
Connection c = DriverManager.getConnection(
"jdbc:sqlserver://응.database.windows.net:1433; databaseName=응; user=응; password=gmldnjs4@;");
PreparedStatement ps = c.prepareStatement(
"insert into user(id, name, password) values(?,?,?)");
ps.setString(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getPassword());
ps.executeUpdate();
}catch (NullPointerException ne){
ne.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally {
if(user.getId()!=null && user.getName() != null && user.getPassword() != null){
ps.close();
c.close();
}
}
}" correct please
The updated code looks better. Here are a few additional comments:
Connection string
The connection string you're using looks correct, assuming that your SQL Server instance is listening on port 1433 and that you have a valid username and password for the 응database.
Exception handling
Your exception handling looks good - you're catching specific exceptions and printing the stack trace, which is a good way to debug issues. However, it's unclear what you're trying to achieve with the NullPointerException catch block. It's best to avoid catching NullPointerExceptions unless you have a good reason to do so, as they typically indicate a programming error that should be fixed.
Resource cleanup
The finally block is a good way to ensure that database resources are cleaned up properly, regardless of whether an exception occurs or not. However, it's possible that the ps and c variables may be null if an exception is thrown before they are initialized. To avoid this, you can declare them outside the try block and assign null as their initial value, like this:
java
Copy code
Connection c = null;
PreparedStatement ps = null;
try {
c = DriverManager.getConnection(/* connection string */);
ps = c.prepareStatement(/* SQL statement */);
// ...
} catch (SQLException e) {
// handle exception
} finally {
try { ps.close(); } catch (Exception e) { }
try { c.close(); } catch (Exception e) { }
}
This ensures that the close() method is only called on initialized objects.
Overall, the code looks good and should be able to add a new user to your SQL Server database.
Kim
'ps' and 'c' cannot find symbol
I apologize for that mistake. You need to declare the ps and c variables outside of the try-catch block to access them in the finally block. Here's the corrected code:
java
Copy code
public class UserDao {
public void add(User user) throws SQLException {
Connection c = null;
PreparedStatement ps = null;
try {
c = DriverManager.getConnection(
"jdbc:sqlserver://응.database.windows.net:1433; databaseName=응; user=응; password=gmldnjs4@;");
ps = c.prepareStatement(
"insert into user(id, name, password) values(?,?,?)");
ps.setString(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getPassword());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (ps != null) {
ps.close();
}
if (c != null) {
c.close();
}
}
}
}
In this corrected code, the ps and c variables are declared before the try-catch block and initialized to null. Then, in the finally block, it checks whether the objects have been initialized before attempting to call the close() method on them. This ensures that the close() method is only called on initialized objects, avoiding the "cannot find symbol" error.
Kim
why should i check 'ps' and 'c' null
It's important to check whether ps and c are null before calling their close() method in the finally block because there's a chance that an exception could be thrown before they're initialized.
For example, if an exception is thrown when you're establishing a connection to the database, then the ps and c variables will remain null. If you don't check whether they're null before calling their close() method in the finally block, then you'll get a NullPointerException when the method tries to call close() on an uninitialized object.
By checking whether ps and c are null before calling their close() method, you ensure that the close() method is only called on initialized objects, avoiding the NullPointerException.
Kim
"java.sql.SQLException: No suitable driver found for jdbc:sqlserver://응.database.windows.net:1433; databaseName=응; user=응; password=gmldnjs4@;" occurs
The error "java.sql.SQLException: No suitable driver found" occurs because the JDBC driver for SQL Server has not been loaded. You need to make sure that you have added the JDBC driver JAR file to your project's classpath.
Here are the steps to add the JDBC driver JAR file to your project's classpath:
Download the JDBC driver JAR file for SQL Server from the Microsoft website.
Add the JDBC driver JAR file to your project's classpath. You can do this in different ways depending on the build tool you are using. Here are some examples:
If you're using Maven, you can add the following dependency to your pom.xml file:
php
Copy code
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>9.4.1.jre15</version>
</dependency>
If you're using Gradle, you can add the following dependency to your build.gradle file:
python
Copy code
implementation 'com.microsoft.sqlserver:mssql-jdbc:9.4.1.jre15'
If you're not using any build tool, you can add the JDBC driver JAR file to your project's classpath manually. You can do this by copying the JAR file to a directory that's included in your project's classpath, such as the lib directory in your project's root directory.
Once you have added the JDBC driver JAR file to your project's classpath, try running your code again. The error "java.sql.SQLException: No suitable driver found" should be resolved.
Kim
im using gradle and i added this jdbc zip file"sqljdbc_12.2.0.0_kor" on project structure -> libraries
When using Gradle, you can include the JDBC driver JAR file as a dependency in your build.gradle file.
Assuming you have added the sqljdbc_12.2.0.0_kor JDBC driver ZIP file to the lib directory of your project, you can add the following dependencies to your build.gradle file:
java
Copy code
dependencies {
// other dependencies...
compile files('lib/sqljdbc_12.2.0.0_kor/sqljdbc_12.2/enu/mssql-jdbc-9.4.1.jre15.jar')
}
This assumes that the JAR file inside the ZIP file is called mssql-jdbc-9.4.1.jre15.jar. If the actual name of the JAR file is different, you should replace the filename in the compile files() line accordingly.
Once you have added the dependency, run gradle build to download and include the JDBC driver JAR file in your project.
